Linq To Stored Procedure Example

People asked for a more concrete example of how to map stored procedure using LinqToAnything.

I deliver!

OK, so suppose you have a sproc, which takes some filter parameters, and skip/take for paging e.g.

ALTER PROCEDURE dbo.GetUsers(@RoleId int = null, @skip int = 0, @take int)  
AS  
SELECT * FROM Users WHERE RoleId = ISNULL(@RoleId, RoleId)  
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY  

And here is code for wrapping the sproc using LinqToAnything (I've supposed you have a Entity Framework data context which calls the sproc)

public IQueryable<User> GetUsersQueryable()  
{
    return new DelegateQueryable<User>(queryInfo => {

        var roleId = queryInfo.Clauses.OfType<Where>()
            .Where(c => c.PropertyName == "RoleId" && c.Operator == "Equals")
            .Select(c => c.Value as int?)
            .SingleOrDefault();

        return _timeSheetDataContext.Users
            .SqlQuery("GetUsers @RoleId, @Skip, @Take", roleId, queryInfo.Skip, queryInfo.Take);
    });
}

You can now use this method to get a real IQueryable so you can do things like

    var q = GetUsersQueryable();

    var users = q.Skip(1).Take(10);

    var adminRoleId = 123;
    var user = q.Where(u => u.RoleId == adminRoleId).SingleOrDefault();

Or you can pass it into an IQueryable compatible library like Mvc.JQuery.DataTables