Refer to a Child item using LINQ inline SELECT-CodePudding

I am building a custom model from a List of Objects and the code i am using is like below

var dataset = await query.Select(c => new ContactListModel
            {
                Id = c.Id,
                FirstName = c.FirstName,
                LastName = c.LastName,
                Email = c.Email,
                EmailAlternative = c.EmailAlternative,
                JobTitle = c.JobTitle,
                CompanyName = (c.Company != null) ? c.Company.Name : "",
                AddressCountry = c.Addresses.FirstOrDefault(a=>a.IsPrimary) != null ? c.Addresses.FirstOrDefault(a => a.IsPrimary).Country.Name : "",
                AddressCounty = c.Addresses.FirstOrDefault(a => a.IsPrimary) != null ? c.Addresses.FirstOrDefault(a => a.IsPrimary).CountyText : "",
                AddressPostCode = c.Addresses.FirstOrDefault(a => a.IsPrimary) != null ? c.Addresses.FirstOrDefault(a => a.IsPrimary).PostCode : ""                    
            }).OrderByDynamic(sortBy, sortDirection).Paginate((tableState.Page * tableState.PageSize), tableState.PageSize).ToListAsync();

While assign properties AddressCountry , AddressCounty and AddressPostCode (there are 2,3 more properties like this needs to be assigned from PrimaryAddress instance)
i am referring to the PrimaryAddress item from the list of available address values binded to the object

Is there any way to avoid the repetition of filtering to PrimaryAddress each time when setting the property

I tried to add one more property PrimaryAddress of type ContactAddress and then inside the above select statement i assigned the value as


var dataset = await query.Select(c => new ContactListModel
            {
              -----
              PrimaryAddress = c.Addresses.FirstOrDefault(a => a.IsPrimary),
              AddressCountry = PrimaryAddress.Country.Name,
              AddressCounty = PrimaryAddress.CountyText,
              --
            }).OrderByDynamic(sortBy, sortDirection).Paginate((tableState.Page * tableState.PageSize), tableState.PageSize).ToListAsync();

But the error i am getting is the name 'PrimaryAddress' does not exists in the current context

OrderByDynamic & Paginate is for handling the Sorting and pagination and they are helper extensions I used.

CodePudding user response：

The most convenient way to do this is to switch to query syntax and use let:

var dataset = await (
    from c in query
    let primaryAddress = c.Addresses.FirstOrDefault(a => a.IsPrimary)
    select new ContactListModel
    {
        Id = c.Id,
        FirstName = c.FirstName,
        LastName = c.LastName,
        Email = c.Email,
        EmailAlternative = c.EmailAlternative,
        JobTitle = c.JobTitle,
        CompanyName = c.Company.Name,
        AddressCountry = primaryAddress.Country.Name,
        AddressCounty = primaryAddress.CountyText,
        AddressPostCode = primaryAddress.PostCode                   
    }).ToListAsync();

Note that I removed the null checks. If query is an IQueryable from Entity Framework (or another O/R mapper) then it will be translated into SQL, which eliminates the need for null checks. If query is an in-memory list of objects you can use c.Company?.Name, etc.

Also note that you may want to order Addresses by some property so you have control over which address is "first".

The same goal can be achieved by using a from - from query (or, SelectMany under the hood):

var dataset = await (
    from c in query
    from primaryAddress in c.Addresses.Where(a => a.IsPrimary).Take(1)
    select new ContactListModel
    {
        Id = c.Id,
        FirstName = c.FirstName,
        LastName = c.LastName,
        Email = c.Email,
        EmailAlternative = c.EmailAlternative,
        JobTitle = c.JobTitle,
        CompanyName = c.Company.Name,
        AddressCountry = primaryAddress.Country.Name,
        AddressCounty = primaryAddress.CountyText,
        AddressPostCode = primaryAddress.PostCode                   
    }).ToListAsync();

If query is indeed an IQueryable it may be worth while investigating which of both alternatives generates the best SQL in terms of the the database engine's query execution plan. In EF core, the second alternative only generates one subquery to get the primary address, whereas the first one does that for each primaryAddress property.

CodePudding user response：

what about this

              .....
             PrimaryAddress = c.Addresses.Select(x=>
              new PrimaryAddress {
                                  Country = x.Country.Name,
                                  County = x.CountyText
                                  }).FirstOrDefault(a => a.IsPrimary),
            CompanyName = (c.Company != null) ? c.Company.Name : ""
            }).ToListAsync();

you can leave address as it is, or you can use AddressCountry, AddressCounty, AddressPostCode, you can get them through the ContactListModel getters

CodePudding user response：

By double select, I mean:

var dataset = await query.Select(c => new { c, PA = c.Addresses.FirstOrDefault(a => a.IsPrimary) })
.Select(at => new ContactListModel
{
    Id = at.c.Id,
    FirstName = at.c.FirstName,
    LastName = at.c.LastName,
    Email = at.c.Email,
    EmailAlternative = at.c.EmailAlternative,
    JobTitle = at.c.JobTitle,
    CompanyName = at.c.Company,
    AddressCountry = at.PA.Country.Name,
    AddressCounty = at.PA.CountyText,
    AddressPostCode = at.PA.PostCode : ""                    
})

It's the method syntax form of what Gert posted in the query syntax (first example)

I'm no fan of the SQL generated (in either this case or Gert's first), something like (not exact but should give the idea):

SELECT
  c.Id,
  ...
  (SELECT TOP 1 Name FROM Addresses WHERE Id = c.AddressId AND IsPrimary = 1) Name,
  (SELECT TOP 1 Country FROM Addresses WHERE Id = c.AddressId AND IsPrimary = 1) Country,
  (SELECT TOP 1 PostCode FROM Addresses WHERE Id = c.AddressId AND IsPrimary = 1) PostCode
FROM
  Contacts c

Hopefully SQLS can optimize that to just a single lookup.. I think I'd prefer a filtered include for a more straightforward JOIN and use a mapping library (or null coalescing) to extract the data C# side. Drop a comment after you add the info as to your EF(C) version so it's clear whether that's an option