0

I have a database I'm trying to query via Entity Framework and then select the return data into an collection output object - while using IQueryable to improve performance - and then apply a couple functions to the resultant data that comes back from the DB query. For one, I can't use .ToLocalTime() on a DateTime object as it's not supported. I get this error:

LINQ to Entities does not recognize the method 'System.DateTime ToLocalTime()' method, and this method cannot be translated into a store expression.

If I try to call other custom functions on returned data I get a similar exception.

Here is my code below:

IQueryable<DatabaseEntityType> query = context.MyEntities.Where(c=> c.Finished == true);

db.MyEntities.OrderByDescending(c => c.DateCreated).Select(c => new OutputObject()
{
    Guid = c.Guid,
    Notes = c.Notes,
    DateCreated = c.DateCreated.ToLocalTime(),
    Related = String.Join("<br/>", c.RelatedEntities.Select(c => c.TractNumber)),
    Others = String.Join("<br/>", c.OtherRelatedEntities.Select(c => c.OwnerName)),
    DocumentType = EnumUtility.GetDocumentTypeString(c.DocumentType),
    Uploader = c.Account.FirstName + " " + c.Account.LastName,
});

Is there any way to do this while still using IQueryable? I have a large data set that needs to be filtered on the server without using a .ToList().

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
Pugz
  • 939
  • 3
  • 11
  • 25
  • You try AsEnumerable() http://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable – Steve Greene Jul 14 '15 at 17:35
  • Doesn't AsEnumerable fetch the entities just like ToList? Wouldn't help me if that's the case. – Pugz Jul 14 '15 at 17:36
  • No. http://stackoverflow.com/questions/3775351/asp-net-mvc2-with-entity-framework-4-asenumerable-or-tolist-in-repository – Steve Greene Jul 14 '15 at 17:42
  • 1
    From your code example it doesn't seem that you are using Date manipulation for filtering. If you can avoid it, I would leave the presentation code (making your results look pretty for display) to c#, not sql. And if you can't do that, make a View in sql and map that in EF. – gunr2171 Jul 14 '15 at 17:55
  • I'd second @gunr2171's comment. Also, you generally can't just have any C# function running on the database server (which is what happens when your still using an `IQueryable`) -- it would have to be something that's translatable as it says. You could also use [DbFunctions](https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions(v=vs.113).aspx) or the [DbFunctionAttribute](https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctionattribute(v=vs.113).aspx) if there's a specific function you want to use, though. – jjj Jul 14 '15 at 18:01
  • @gunr2171- I understand that I can't run C# code on the server. The issue is that I have another library that takes an IQueryable but I need to modify some of the return data from the SQL query before it goes into this other library. Any thoughts? – Pugz Jul 14 '15 at 19:00
  • @jjj see my comment to gunr2171 – Pugz Jul 14 '15 at 19:00
  • Well, if the other library doesn't need to run stuff on the data source, you could use `AsEnumerable`, do your data manipulation, then use `AsQueryable` so that the library could be used. Of course, the entities would be loaded from the data source during the data manipulation. – jjj Jul 15 '15 at 00:38
  • Yeah it is Telerik UI for MVC and they have a DataSourceRequest object that comes back from the client... You then run a method on that object which directly queries the database via the IQueryable... I really need to be able to filter directly on the datasource. We're dealing with 10s of millions of rows. – Pugz Jul 15 '15 at 00:47
  • You can read more about DataSourceRequest here in case I'm missing something http://docs.telerik.com/kendo-ui/aspnet-mvc/helpers/grid/faq – Pugz Jul 15 '15 at 00:49
  • Maybe you should add that to your question as well. I'm thinking there should be a way to use Telerik's library and still do the formatting you're trying to do after pulling data from the data source. – jjj Jul 15 '15 at 08:03

0 Answers0