c# - 具有条件连接和非匿名返回的 LINQ 查询

Question

我有一个针对 SQL Server 数据库的 LINQ 查询，它将每一行的数据写入对象 Person。在某些情况下，我想加入额外的表并添加更多 Person 对象的字段，同时利用 LINQ 延迟加载。

Person 类如下所示:

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
    ...
}

我试过使用三元运算符，但条件不是立即求值，而是发送到 SQL Server 在那里求值，这样即使不需要也可以执行连接。

// 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
    ...
});

在 if 子句中编写连接语句并在每次连接后调用 Person 构造函数，但不是很高效和优雅:

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)
...

感谢您的帮助!

Answer 1

我建议你离开你这样的 Person 模型......

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

然后创建一个 View 模型类，其中包含来自其他表的可能的额外字段。像这样将 Person 模型作为构造函数参数传递。

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; }
}

现在，运行一个包含所有可能的依赖表的连接查询，然后使用结果像这样填充 PersonViewModel...

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);

希望对您有所帮助。