Joining table to a list using Entity Framework

Question

I have the following Entity Framework function that it joining a table to a list. Each item in serviceSuburbList contains two ints, ServiceId and SuburbId.

public List<SearchResults> GetSearchResultsList(List<ServiceSuburbPair> serviceSuburbList)
{
    var srtList = new List<SearchResults>();
    srtList = DataContext.Set<SearchResults>()
                         .AsEnumerable()
                         .Where(x => serviceSuburbList.Any(m => m.ServiceId == x.ServiceId && 
                                                                m.SuburbId == x.SuburbId))
                         .ToList();

    return srtList;
}

Obviously that AsEnumerable is killing my performance. I'm unsure of another way to do this. Basically, I have my SearchResults table and I want to find records that match serviceSuburbList.

Why do you even need that .AsEnumerable? I thought I already is Enumerable... — Hooman Bahreini, Jul 09 '18 at 01:30
It doesn't work if I remove AsEnumerable. I get 0 results if I do that — CaptainMorgan, Jul 09 '18 at 01:31
Possible duplicate of [EntityFramework - contains query of composite key](https://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key) — Lowkey, Jul 09 '18 at 04:02
you should look at the generated query. The .AsEnumerable does nothing (except materialize early), so if the results differ there might be a problem in your EF provider. — DevilSuichiro, Jul 09 '18 at 04:44
I would look like to see the code that creates serviceSuburbList parameter. If it's coming out of the same context I would suggest, rather than generating two separate queries you should be passing a queryable object and execute it in a single query i.e. change the serviceSuburbList parameter from List<> to IQueryable. I think you are not understanding Entity Framework materialisation — Mick, Jul 09 '18 at 08:23

Slava Utesinov · Answer 1 · 2018-07-09T06:26:51.527

If serviceSuburbList's length is not big, you can make several Unions:

var table = DataContext.Set<SearchResults>();
IQuerable<SearchResults> query = null;
foreach(var y in serviceSuburbList)
{
    var temp = table.Where(x => x.ServiceId == y.ServiceId && x.SuburbId == y.SuburbId);
    query = query == null ? temp : query.Union(temp);
}

var srtList = query.ToList();

Another solution - to use Z.EntityFramework.Plus.EF6 library:

var srtList = serviceSuburbList.Select(y => 
                 ctx.Customer.DeferredFirstOrDefault(
                    x => x.ServiceId == y.ServiceId && x.SuburbId == y.SuburbId
                 ).FutureValue()
              ).ToList().Select(x => x.Value).Where(x => x != null).ToList();
//all queries together as a batch will be sent to database 
//when first time .Value property will be requested

Joining table to a list using Entity Framework

1 Answers1