CODEWITHSUNDEEP
c#linqjoinlinq-to-sql
bahne
bahne

Reputation: 41

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!

Upvotes: 3

Views: 765

Answers (3)

bahne
bahne

Reputation: 41

The custom extension Ivan posted in the comment to my question solved the problem. It removes the instruction to join if the condition in the ternary operator evaluates to false.

Upvotes: 0

Ben
Ben

Reputation: 63

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.

Upvotes: 1

Ian Robertson
Ian Robertson

Reputation: 2812

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

LinqKit on GitHub

LinqKit on Nuget

Upvotes: 0

Related Questions