Reputation: 41
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
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
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
Reputation: 2812
Have a look at LinqKit - AsExpandable + Predicate Builders + Sub-queries
Upvotes: 0