CODEWITHSUNDEEP
c#.netlinqentity-framework-core
SkyeBoniwell
SkyeBoniwell

Reputation: 7102

replace joins with navigation properties

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;

Upvotes: 1

Views: 297

Answers (1)

Arsen Khachaturyan
Arsen Khachaturyan

Reputation: 8330

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.

Upvotes: 1

Related Questions