CODEWITHSUNDEEP
c#performanceentity-frameworklinq
fdsafdsafdsafdsafs
fdsafdsafdsafdsafs

Reputation: 169

Getting the right results from a LINQ statement

I am using a LINQ statement to get a collection of data from a table. I need data from other related tables, to get the set I am looking for. I am using Entity Framework 6. Am I way off on this? I could use some help sorting this out. I have masked my code for business reasons, so the table names and values are fake:

var result = POLICY_Record
                           .Include(pt => pt.POLICY_Primer)
                           .Include(pt => pt.POLICY_Primer.LOOKUP)
                           .Where(pt => pt.LOOKUP.ABBREV == "XXX")
                           .Where(pt => pt.DATE >= startDate && pt.DATE <= endDate)
                           .Where(pt => pt.LOOKUP.TYPE == "Y")
                           .Where(pt => pt.STATUS == 1)
                           .Where(pt => !excludedTransTypes.Contains(pt.FK_Value))
                           .Where(pt => stateconfigs.Contains(pt.STATE))
                           .ToList();

My issue is that that there may be more than 1 Policy_Record records per Policy_Primer. When I run it like this, I have to dig deep to get the actual results. I have to iterate through the results to pull the Primer record and then get the Policy Records.

If I include search for a single Primer ID, I get the collection of Records for that id right away. If I don't, I am forced to iterate.

var result = POLICY_Record
                           .Include(pt => pt.POLICY_Primer)
                           .Include(pt => pt.POLICY_Primer.LOOKUP)
                           .Where(pt => pt.LOOKUP.ABBREV == "XXX")
                           .Where(pt => pt.DATE >= startDate && pt.DATE <= endDate)
                           .Where(pt => pt.LOOKUP.TYPE == "Y")
                           .Where(pt => pt.STATUS == 1)
                           .Where(pt => !excludedTransTypes.Contains(pt.FK_Value))
                           .Where(pt => stateconfigs.Contains(pt.STATE))
                           .Where(pt => pt.POLICY_Primer.NUMBER == Num)
                           .ToList();

Oddly, If I mouse over the result variable, it lists it as a List of POLICY_Record. Am I just doing this in the wrong order? If I start with the Primer, the where conditions fail and I cannot find a way to get them back.

Upvotes: 0

Views: 61

Answers (1)

Steve Py
Steve Py

Reputation: 34793

Oddly, If I mouse over the result variable, it lists it as a List of POLICY_Record. Am I just doing this in the wrong order?

There is nothing odd about this. I suspect you are a bit confused by what Linq is doing. Linq reduces sets, so we'll decompose your original query:

var result = POLICY_Record
                       .Include(pt => pt.POLICY_Primer)
                       .Include(pt => pt.POLICY_Primer.LOOKUP)
                       .Where(pt => pt.LOOKUP.ABBREV == "XXX")
                       .Where(pt => pt.DATE >= startDate && pt.DATE <= endDate)
                       .Where(pt => pt.LOOKUP.TYPE == "Y")
                       .Where(pt => pt.STATUS == 1)
                       .Where(pt => !excludedTransTypes.Contains(pt.FK_Value))
                       .Where(pt => stateconfigs.Contains(pt.STATE))
                       .ToList();

starting with..

var result = POLICY_Record

I'm assuming that POLICY_Record in this case is referring to a DbSet of your policy records. Without a Select or SelectMany, your Linq expression will return the entities you choose to start with.

                       .Include(pt => pt.POLICY_Primer)
                       .Include(pt => pt.POLICY_Primer.LOOKUP)

This tells EF that you want to eager-load the Policy_Primer, and that primer's Lookup entity for each and every Policy_Record it fetches back. This is not required to be able to write conditions against these fields, or even to project your output. It is only applicable if you want to return Policy_Record entities and have these related entities pre-loaded and ready to go. (As opposed to lazy loaded on demand, requiring extra round-trips to the database.)

                       .Where(pt => pt.LOOKUP.ABBREV == "XXX")
                       .Where(pt => pt.DATE >= startDate && pt.DATE <= endDate)
                       .Where(pt => pt.LOOKUP.TYPE == "Y")
                       .Where(pt => pt.STATUS == 1)
                       .Where(pt => !excludedTransTypes.Contains(pt.FK_Value))
                       .Where(pt => stateconfigs.Contains(pt.STATE))

These are your criteria. You are telling EF to return Policy_Record entities that contain data that satisfy all of these conditions. From here we are not basing any of that criteria on the additional eager-loaded fields like the primer.

                       .ToList();

This "executes" the query, and tells EF to return any and all Policy_Records that satisfy all of the Where conditions, and with those records, include each of their Policy_Primer, and that primer's Lookup.

The next question would be: If this isn't what you want, what do you actually expect to see? By adding:

                       .Where(pt => pt.POLICY_Primer.NUMBER == Num)

This told EF that you only want Policy_Records where their associated primer number matched your parameter. You'll still get Policy_Records, just the ones that contain the primer with that number.

Projection: This is probably the missing piece. The above queries reduced the set of Policy_Records, now we use projection to tell EF to return what we want. This is typically done by using Select. For instance if we wanted just the Policy Primers for all of those matching policies we would add:

                       .Select(pt => pt.POLICY_Primer)

so..

var result = POLICY_Record
                       .Include(pt => pt.POLICY_Primer)
                       .Include(pt => pt.POLICY_Primer.LOOKUP)
                       .Where(pt => pt.LOOKUP.ABBREV == "XXX")
                       .Where(pt => pt.DATE >= startDate && pt.DATE <= endDate)
                       .Where(pt => pt.LOOKUP.TYPE == "Y")
                       .Where(pt => pt.STATUS == 1)
                       .Where(pt => !excludedTransTypes.Contains(pt.FK_Value))
                       .Where(pt => stateconfigs.Contains(pt.STATE))
                       .Select(pt => pt.POLICY_Primer)
                       .ToList();

Instead of a list of policy records, we get a list of those record's primers. We could add a .Distinct() after the Select to remove duplicate primers if many of the policies could share the same primer.

Projection is useful for reducing the total amount of data returned, and flattening data to reflect just the structure we want. For instance if we are displaying a list of policies and their respective primers, and we only care about some of the fields we can define a simple view model for the search results and use Select to populate those:

.Select(pt => new PolicyViewModel
{
    PolicyNumber = pt.Policy_Number,
    Date = pt.Date,
    PrimerNumber = pt.Policy_Primer.Number,
    // ...
})

When using Select to project like this we tell EF exactly what fields from what entities we want, and it will compose a query to retrieve just those details. This allows databases to leverage indexes more efficiently and reduces the amount of time, memory, and bandwidth the queries need on both the database server and app server. When using projection in this way you also negate the need for using Include. EF will join tables automatically as necessary to retrieve the data you request. You can include entities, but generally should avoid doing so, as it removes the benefits of reducing the dataset size, and also can leave you open to lazy load hits and other nasties if you return entities.

This should hopefully give you some ideas on what might be missing from your scenario, but feel free to clarify your question or expand on it with what you're expecting to get from the query.

Upvotes: 1

Related Questions