LINQ Query with conditional join and non-anonymous return

I have a LINQ query against a SQL Server database that writes the data of each row into an object Person. Under certain conditions, I want to join with additional tables and add some more fields of the Person objects, all while taking advantage of LINQs deferred loading.

The Person class looks like this:

public class Person 
{
    // Data provided by Persons table
    public string Name { get; set; }
    public string CityName { get; set; }
    public string JobName { get; set; }

    // Data provided by Cities table
    public int? CityPopulation 

    // Data provided by Jobs table
    public int? AverageSalary

    // Data from other tables
    ...
}

I have tried using the ternary operator, but the condition is not evaluated right away, but sent to SQL Server to be evaluated there, so that the join is executed even if it is not required.

// Fill values provided by Person table
IQueryable<Person> query;

query = dbContext.Persons.Select(x => new Person
{
    Name = x.Name,
    CityName = x.CityName,
    JobName = x.JobName,

    // Get data from City table, perform join to Cities only when cityRequired
    CityPopulation = cityRequired ? x.Cities.Population : (int?) null,
    ...

    // Get data from Job table, perform join to Jobs only when jobsRequired 
    JobAverageSalary = jobRequired ? x.Jobs.AverageSalary : (int?) null,
    ...

    // Get data from other tables
    ...
});

Writing the join statements in an if clause and calling the Person constructor after every join works, but is not very efficient and elegant:

IQueryable<Person> query;

query = dbContext.Persons.Select(x => new Person
{
    Name = x.Name,
    City = x.CityName,
    Job = x.JobName,
}

if(cityRequired)
{
    query = query.Join(dbContext.Cities, Person => Person.CityName, City => City.Name, (Person, City) => new Person 
    {
        // Copying old values
        Name = Person.Name,
        CityName = Person.CityName,
        JobName = Person.Jobname,

        // Filling in new values from City
        CityPopulation = City.Population,
    }
}

if (jobRequired)
...

I appreciate any help!

2 answers

  • answered 2018-01-14 11:35 Ian Robertson

    Have a look at LinqKit - AsExpandable + Predicate Builders + Sub-queries

    LinqKit on GitHub

    LinqKit on Nuget

  • answered 2018-01-14 15:42 Ben

    I suggest you leave you Person model like this...

    public class Person
    {
        // Data provided by Persons table
        public string Name { get; set; }
        public string CityName { get; set; }
        public string JobName { get; set; }
    }
    

    Then create a view model class with the possible extra field from other tables. Passing the Person model as a constructor parameter like this.

    public class PersonViewModel
    {
        public PersonViewModel(Person person)
        {
            Name = person.Name;
            CityName = person.CityName;
            JobName = person.JobName;
        }
    
        public string Name { get; set; }
        public string CityName { get; set; }
        public string JobName { get; set; }
    
        public int? CityPopulation { get; set; }
        public int? AverageSalary { get; set; }
    }
    

    Now, run a join query that includes all the possible dependent tables and then use the result to fill the PersonViewModel like this...

    var query = dbContext.Persons
                .Join(dbContext.Cities, person => person.CityName, city => city.Name,
                    (person, city) => new {person, city}).Join(dbContext.Jobs, person => person.person.JobName,
                    job => job.Name, (person, job) => new {person, job}).FirstOrDefault();
    
    var personViewModel = new PersonViewModel(query.person.person)
    {
        // Get data from City table, perform join to Cities only when cityRequired
        CityPopulation = cityRequired ? query.person.city.Population : (int?) null,
    
        // Get data from Job table, perform join to Jobs only when jobsRequired
        AverageSalary = jobRequired ? query.job.AverageSalary : (int?) null
    };
    return View(personViewModel);
    

    I hope this helps.