replace joins with navigation properties

Question

At the advice from people on here, I am trying to rewrite the linq query below using Navigation Properties.

I haven't gotten very far, because I'm not sure how to replace the joins in my Linq query with Navigation Properties.

So far I have this:

await _context.StarList(sl => new 
{
    sl.StarId,
    sl.StarType,
    sl.StarTitle,
    sl.ChemicalId...
}

But then, in the original query, I start using 'chemicalatoms' in the query.

So I'm not sure how to transition to them.

So my question is, how do I replace the joins in my original query with Navigation Properties?

Thanks!

 public async Task<ActionResult<object>> GetStarChemicalData(string starID)
    {
        var starChemicalData = await (from starlist in _context.StarList
                                      join ql in _context.ChemicalList on starlist.ChemicalId equals ql.ChemicalId into stars
                                      from chemicallist in stars.DefaultIfEmpty()
                                      join qc in _context.ChemicalAtoms on chemicallist.ChemicalId equals qc.ChemicalId into chemicals
                                      from chemicalatoms in chemicals.DefaultIfEmpty()
                                      join nk in _context.StarLinks on chemicalatoms.AtomId equals nk.AtomId into links
                                      from starlinks in links.DefaultIfEmpty()
                                      where starlist.StarId == starID
                                      select new
                                      {
                                          StarId = starlist.StarId,
                                          StarType = starlist.StarType,
                                          StarTitle = starlist.StarTitle,
                                          ChemicalId = starlist.ChemicalId,
                                          AtomId = (Guid?)chemicalatoms.AtomId,
                                          OrderId = chemicalatoms.OrderId,
                                          ChemicalText = chemicallist.ChemicalText,
                                          AtomText = chemicalatoms.AtomText,
                                          Wavelength = chemicalatoms.Wavelength,
                                          isRedShifted = (starlinks.AtomId != null && starlist.StarType == 1) ? 1
                                          : (starlinks.AtomId == null && starlist.StarType == 1) ? 0
                                          : (int?)null
                                      })
                                          .GroupBy(x => x.StarId)
                                          .Select(g => new
                                          {
                                              StarId = g.FirstOrDefault().StarId,
                                              StarType = g.FirstOrDefault().StarType,
                                              StarTitle = g.FirstOrDefault().StarTitle,
                                              ChemicalId = g.FirstOrDefault().ChemicalId,
                                              ChemicalText = g.FirstOrDefault().ChemicalText,
                                              ChemicalAtoms = (g.FirstOrDefault().AtomId != null ? g.Select(x => new
                                              {
                                                  AtomId = x.AtomId,
                                                  OrderId = x.OrderId,
                                                  AtomText = x.AtomText,
                                                  Feedback = x.Wavelength,
                                                  IsCorrect = x.isRedShifted
                                              }) : null)
                                          }).FirstOrDefaultAsync();

        return starChemicalData;

Please check [this](https://stackoverflow.com/questions/12284719/linq-include-vs-join-are-they-equivalent) post for more information. — Arsen Khachaturyan, Aug 26 '20 at 08:54
@ArsenKhachaturyan So instead of doing a "join" like this: `from starlist in _context.StarList join ql in _context.ChemicalList` , I'd replace that with `_context.StartList.Include("_context.ChemicalList")`? Thanks! — SkyeBoniwell, Aug 26 '20 at 13:46

Arsen Khachaturyan · Accepted Answer · 2020-08-26T15:05:45.013

1

As per the above communication succeeded, I think I can sum up the results, here )) So, instead of having this:

var starChemicalData = await (from starlist in _context.StarList
    join ql in _context.ChemicalList on starlist.ChemicalId equals ql.ChemicalId into stars
    from chemicallist in stars.DefaultIfEmpty()
    join qc in _context.ChemicalAtoms on chemicallist.ChemicalId equals qc.ChemicalId into chemicals
    from chemicalatoms in chemicals.DefaultIfEmpty()
    join nk in _context.StarLinks on chemicalatoms.AtomId equals nk.AtomId into links 
    ...
    );

we can have this:

_context.StarList.Include(st => st.ChemicalList)
    .Include(ca => ca.ChemicalAtoms)  // or ThenInclude, based on your relations
    .Include(sl => sl.StartLinks)
    ...
    Select(r => new {...})
    ...

Please note that what is essential when using Include is that your relations(primary foreign key) should be set appropriately between your database tables you are using here.

Another important thing is that Include() will not bring a row result in case if one of your PK⟶FK relations contains null value, which it actually shouldn't.

edited Aug 26 '20 at 15:05

answered Aug 26 '20 at 14:13

Arsen Khachaturyan

7,904
4
42
42

Oh ok so if I want to use Include(), I need to make sure that each table has a primary key and that the primary key is also the foreign key in any table I want to include? ThankS! For example, ChemicalList would have a primary key of ChemicalId. Which would also be a foreign key in StarList? – SkyeBoniwell Aug 26 '20 at 15:20
Yeap, so that's how basically EF does the magic because otherwise, it will not know how the 2 tables are connected. In those scenarios, if the tables don't have relations properly set you can you the `join` (or when you want to make `LEFT OUTER JOIN` like in SQL), but otherwise save your time using the `Include` method. – Arsen Khachaturyan Aug 26 '20 at 15:38
But this doesn't answer the question. `Include` is an example of how to *use* navigation properties. You don't answer the question how to convert LINQ `join` statements into navigation properties. – Gert Arnold Aug 26 '20 at 15:50
Yes, I didn't convert the whole query, but I think I've shown an example of how to do that (check above) using the navigation properties with `Include`. – Arsen Khachaturyan Aug 26 '20 at 15:56

replace joins with navigation properties

1 Answers1