Joining two tables using Include() with conditional filter

c# entity-framework-6 linq sql

Question

I have existing code to update. I want to join another table called Table3. Since the query has an include to Table2, I want to add another .Include with a conditional filter and avoid a left join.

When I add the .include with the .where, I can't access t3Id. Intellisense just shows the table, Table3 and not the Id field.

Did I miss a syntax? Thanks.

Table1 has a key called t1Id.

 var query = (from e in ctx.Table1
    .Include(r => r.Table2.Select(p => p.name))
    .Include(rj => rj.Table3).Where(s => s.t1Id == t3Id)
    select e).ToList();

Table1 will have the following:

Id  name  
1   Joe   
2   Mary
3   Harry

Table3 will have the following:

t1Id   title
3      staff
3      fulltime

Expected Outcome:

1  Joe
2  Mary
3  Harry  [{2, staff}, {3, fulltime}]

Since Harry has a record in the mapping table, he will have an array of Table3 rows.

1
3
8/7/2019 5:13:46 PM

Popular Answer

It is better to use Select instead of Include whenever you can. Select will allow you to query only the properties you really plan to use, making the transfer of the selected data from the database management system to your process faster.

For instance, if you query "Schools with their Students", ever Student will have a foreign key with a value equal to the School's primary key. So if you have School 10, you'll now that all its 5000 Students will have a SchoolId with a value 10. It is a bit of a waste to send this same value 10 over 5000 times.

When querying data, always use Select. Only use Include if you plan to update the fetched data.

Your query (in method syntax):

var result = dbContext.Table1
    .Where(table1Element => ...)    // only if you don't want all elements of Table1 
    .Select(table1Element => new
    {
         // only select the table1 elements that you plan to use:
         Id = table1Element.Id,
         Name = table1Element.Name,

         // Select the items that you want from Table 2:
         Table2Items = table1Element.Table2
                       .Where(table2Element => ...) // only if you don't want all table2 elements
                       .Select(table2Element => new
                       {
                            // Select only the properties of table2 you plan to use:
                            Id = table2Element.Id,
                            Name = table2Element.Name,
                            ...

                            // the following not needed, you already know the value:
                            // Table1Id = table2Element.table1Id, // foreign key
                       })
                       .ToList(),

         // Table3: your new code:
         Table3Items = table1Element.Table3
                       .Select(table3Element => new
                       {
                            // again: only the properties you plan to use
                            Id = table3Element.Id,
                            ...

                            // the following not needed, you already know the value:
                            // Table1Id = table3Element.table1Id, // foreign key
                       })
                       .ToList(),
    });

You see that it is much easier for the reader to see which properties he gets? If one of the tables is expanded, then the new properties are not fetched in this query, after all: the user apparently didn't need the new properties. They are also not described in the specifications of this function.

Note: Because I used new, my types were anonymous types. You can only use them within your function. If you need to return the fetched data, put the data in a known class:

.Select(table1Element => new Table1Class()
{
    Id = table1Element.Id,
    Name = table1Element.Name,
    ...
});

Again: consider not to fill the foreign keys, as you probably won't use them

0
8/8/2019 2:02:00 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow