Unused dynamic SQL removal

Question

Supose I have the following classes:

public class Person
{
  public int Id {get; set;}
  public string Name {get; set;}
  public IEnumerable<Address> Address {get; set;}
}

public class Address
{
  public int Id {get; set;}
  public string Street {get; set;}
  public int Number {get; set;}
}

And a Dto to fetch data from my database:

public class PersonDto
{
  public string Name {get; set;}
  public string FirstAddressStreet {get; set;}
  public IEnumerable<Address> Address {get; set;}
}

The property "FirstAddressStreet" is there for performance reasons.

Now, I have a service that is responsible for fetching data:

public class MyService
{
  public IEnumerable<PersonDto> GetPeople()
  {
    MyContext.PersonSet.Select(p => new PersonDto
    {
      Name = p.Name,
      FirstAddressStreet = p.Address.OrderBy(s => s.Id).FirstOrDefault().Name,
      Address = p.Address,
    })
  }
}

Now, for performance reasons, there is no need to actually get all the addresses from the person if the user is not interested in it. Using DynamicLinq I can strip parts of the SQL:

...GetPeople().Select("new {Name}").ToListAsync() The above code will generate just the necessary code to fetch the name, so no Join with the Address table will be made. But, if instead I select JUST the Address collection: ...GetPeople().Select("new {Address }").ToListAsync() LINQ has to generate a Join to fetch this information, but it will also try to map the Name and FirstAddressStreet(using an UNION, repeating almost all the query besides the firststreet information) properties, even though is not mapped in the and.

Is there any way to go around this issue? I can provide more specific code if needed

Does this answer your question? [Filtering on Include in EF Core](https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core) — Jim de Vries, Jul 15 '22 at 12:50
Not sure, this does open up some possibilities, I'll check it out, thanks! — Mathias Hemmer, Jul 15 '22 at 12:55

score 0 · Answer 1 · answered Jul 29 '22 at 13:38

Just use a boolean to indicate that you want to include the addresses or not.

Like

public class MyService
{
  public IEnumerable<PersonDto> GetPeople(bool includeAddresses)
  {
    var query = MyContext.PersonSet;
    if (includeAddresses)
    {
      return query.Select(p => new PersonDto
      {
        Name = p.Name,
        FirstAddressStreet = p.Address.OrderBy(s => s.Id).FirstOrDefault().Name,
        Address = p.Address,
      }
    }
    else
    {
      return query.Select(p => new PersonDto
      {
        Name = p.Name,
        FirstAddressStreet = string.Empty
      }
    }
  }
}

Note: For better understanding, use plural on the property:
public IEnumerable<Address> Address -> public IEnumerable<Address> Addresses.

Unused dynamic SQL removal

1 Answers1