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
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