具有条件联接和非匿名返回的LINQ查询 [英] LINQ Query with conditional join and non-anonymous return

问题描述

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

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.

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在那里进行求值，以便即使不需要连接也要执行连接.

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

在if子句中编写join语句，并在每次连接成功后调用Person构造函数，但效率和优雅程度都不高:

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

感谢您的帮助！

推荐答案

Ivan在对我的问题的评论中张贴的自定义扩展名解决了该问题.如果三元运算符中的条件评估为false，则会删除要加入的指令.

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.

这篇关于具有条件联接和非匿名返回的LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！