How to use DbFunctions in LINQ?

Question

I have this LINQ to entity:

var result = (from inspArch in inspectionArchives
              from inspAuth in inspArch.InspectionAuthority
              group new { inspArch, inspAuth } by inspArch.CustomerId into g
              select new
              {
                  clientId = g.Key,
                  id = g.Select(x => x.inspArch.Id).ToArray(),               
                  authId = g.Select(x => x.inspAuth.Id).Distinct().ToArray()
              });

But on run time I get this error:

"LINQ to Entities does not recognize the method 'Int32[] ToArray[Int32](System.Collections.Generic.IEnumerable`1[System.Int32])' method, and this method cannot be translated into a store expression."

I know that I can write my LINQ like this:

var result = (from inspArch in inspectionArchives
              from inspAuth in inspArch.InspectionAuthority
              group new { inspArch, inspAuth } by inspArch.CustomerId into g
              select new
              {
                  clientId = g.Key,
                  id = g.Select(x => x.inspArch.Id),    
                  authId = g.Select(x => x.inspAuth.Id).Distinct()
              }).ToList(); 

And then:

var result2 = (from res in result
               select new
               {
                   clientId = res.clientId,
                   id = res.id.ToArray(),
                   authId = res.authId.ToArray()
               });

It works fine but, it pulls the entire table into memory and then applies the projections, which is not very effective.

So I read about DbFunctions Class; is there any way to use mentioned DbFunctions Class on these rows?

id = g.Select(x => x.inspArch.Id).ToArray(),               
authId = g.Select(x => x.inspAuth.Id).Distinct().ToArray()

instead ToArray() method or another way to make ToArray() method recognizable to LINQ to Entities?

Answer 1

You are so close. There is nothing related to DbFunctions here, all you need to account is how query materialization works.

So let start with the query, removing ToArray() stuff:

var query = (from inspArch in inspectionArchives
             from inspAuth in inspArch.InspectionAuthority
             group new { inspArch, inspAuth } by inspArch.CustomerId into g
             select new
             {
                 clientId = g.Key,
                 id = g.Select(x => x.inspArch.Id),               
                 authId = g.Select(x => x.inspAuth.Id).Distinct()
             });

If you put a breakpoint, you'll see that this is a db sql query at this point, which can also be seen by:

var sqlQuery = query.ToString();

Now the only remaining thing is how to achieve the final projection with those ToArray() calls. Logically the first attempt would be:

var result = query
    .Select(e => new { e.clientId, id = e.Id.ToArray(), authId = e.authId.ToArray() })
    .ToList();

But the result will be the same exception, because EF Linq provider is smart enough to go through all the projections and generate just the final one.

So we need to materialize the query before doing the final projection. Don't use ToList() or ToArray() for that! The cheapest way is to use ToEnumerable() wich will give us a minimum (single item) temporary projected memory object, which in turn we will convert to our our final projection.

So our final (and working) code will be:

var result = query
    .AsEnumerable()
    .Select(e => new { e.clientId, id = e.Id.ToArray(), authId = e.authId.ToArray() })
    .ToList();

Or putting it all together:

var result =
    (from inspArch in inspectionArchives
     from inspAuth in inspArch.InspectionAuthority
     group new { inspArch, inspAuth } by inspArch.CustomerId into g
     select new
     {
          clientId = g.Key,
          id = g.Select(x => x.inspArch.Id),               
          authId = g.Select(x => x.inspAuth.Id).Distinct()
     })
    .AsEnumerable()
    .Select(e => new { e.clientId, id = e.Id.ToArray(), authId = e.authId.ToArray() })
    .ToList();

P.S. When working with EF queries, you'd better off not using ToArray() in projections. Use eventually ToList() or leave them as they are (IEnumerable, IOrderedEnumerable etc.) - EF will materialize them for you using the best container that fits (usually List, that's why ToList() is recognized, while ToArray() not).