LINQ to Entities does not recognize the method(Access to very large database)

Question

I have a database with 2 million records and I have to sort them and output the results in JSON (WebAPI project).

Here is my code:

CODE1

var start = Convert.ToDateTime(startdatetime.Split(new[] { ':' }, 2)[0]).Date;
        var end = Convert.ToDateTime(enddatetime.Split(new[] { ':' }, 2)[0]).Date;

        var result = (from request in db.Requests
                      where ((Convert.ToDateTime(request.DateTime.Split(new[] { ':' }, 2)[0]).Date >= start)
                     &&
                     (Convert.ToDateTime(request.DateTime.Split(new[] { ':' }, 2)[0]).Date <= end))
                      group request.IP_or_Host by request.IP_or_Host into g

                      orderby (g.Count()) descending

                      select g.Key).Distinct().Take(n).ToList();
        return result;

This code throws the error:

LINQ to Entities does not recognize the method

Earlier it was like this:

CODE2

var start = Convert.ToDateTime(startdatetime.Split(new[] { ':' }, 2)[0]).Date;
            var end = Convert.ToDateTime(enddatetime.Split(new[] { ':' }, 2)[0]).Date;



            var result = (from request in db.Requests.ToList()
                          where ((Convert.ToDateTime(request.DateTime.Split(new[] { ':' }, 2)[0]).Date >= start)
                         &&
                         (Convert.ToDateTime(request.DateTime.Split(new[] { ':' }, 2)[0]).Date <= end))
                          group request.IP_or_Host by request.IP_or_Host into g

                          orderby (g.Count()) descending

                          select g.Key).Distinct().Take(n).ToList();
            return result;

But if the database has too many records it throws an outofmemory exception. What do I have to do to retrieve and sort data from the database in a memory efficient manner using LINQ?

You are mixing immediately executed functions with expressions in your query; this isn't allowed in Entity Framework 6 (Core has limited support for it). You should precompute some of your parameters outside the query to a temporary variable. Things like `DateTime.Split`, and `Convert.ToDateTime`. See [this](https://stackoverflow.com/questions/7259567/linq-to-entities-does-not-recognize-the-method?rq=1) answer for more information. — Bradley Uffner, Jan 09 '18 at 01:42
So the DateTime column in db isn't a DateTime type but nvarchar? — Daniel Lorenz, Jan 09 '18 at 03:08
@DanielLorenz yep, i stored DateTime in my database like a string , this format "08/Mar/2004:07:07:13 -0800" , because then i have to sort records by date only , but i have to stored time too. — Ага, Jan 09 '18 at 09:27
@BradleyUffner i know that EF doesnt allow to use fucntion expressions , but i dont know what can i do in this situation. — Ага, Jan 09 '18 at 09:28
if it isn't too late, id just create two columns. one for date and one for time. otherwise, maybe look into SqlFunctuons class. — Daniel Lorenz, Jan 09 '18 at 12:35
@DanielLorenz i did it, but problem remained, cannot use .Date for data from database , how to fix it? — Ага, Jan 09 '18 at 14:46
@DanielLorenz i solved it by added ToList() after "where" query. — Ага, Jan 09 '18 at 21:35

score 1 · Answer 1 · answered Jan 15 '18 at 18:29

For DateTime you can use EntityFunctions or SqlFunctions, depending on version.

How to Call Database Functions

EntityFunctions

The way you solved it with ToList(), you're just loading everything in memory. After using ToList() you'll be able to invoke regular functions, but depending on the ammount of data it can be costly and slow.

If you can manage it, it's better to filter in the database.

Another option would be to add an second column to your database with an actual DateTime. You can do that with an SQL script for old data and change your methods to save on both from now on.

LINQ to Entities does not recognize the method(Access to very large database)

1 Answers1