如何在C#中使用LINQ和Lambda表达式编写对应SQL的正确查询

针对你提供的带IN子查询的SQL语句，两种LINQ写法如下：

1. 基础LINQ查询语法 #

// 对应SQL中的IN子查询
var validDeviceIds = from cd in dbContext.ConfigurationDevice
                     where cd.Type == 1 && cd.TerminalNumber > 0
                     select cd.ConfigurationDeviceID;

// 主关联查询
var queryResult = from c in dbContext.Configuration
                  join cd in dbContext.ConfigurationDevice
                  on c.DeviceID equals cd.ConfigurationDeviceID
                  where c.Parameter == "IPAddress"
                        && validDeviceIds.Contains(c.DeviceID)
                  select new 
                  {
                      IPAddress = c.Value,
                      PrintNo = cd.Number,
                      TerminalNo = cd.TerminalNumber
                  };

2. Lambda表达式写法 #

var queryResult = dbContext.Configuration
    .Join(
        dbContext.ConfigurationDevice,
        c => c.DeviceID,
        cd => cd.ConfigurationDeviceID,
        (c, cd) => new { c, cd }
    )
    .Where(joinItem => 
        joinItem.c.Parameter == "IPAddress"
        && dbContext.ConfigurationDevice
            .Where(cd => cd.Type == 1 && cd.TerminalNumber > 0)
            .Select(cd => cd.ConfigurationDeviceID)
            .Contains(joinItem.c.DeviceID)
    )
    .Select(joinItem => new 
    {
        IPAddress = joinItem.c.Value,
        PrintNo = joinItem.cd.Number,
        TerminalNo = joinItem.cd.TerminalNumber
    });

注意事项：

• 代码中的dbContext请替换为你项目中实际的EF/EF Core数据库上下文实例
• 若调用结果列报错，优先检查两点：一是实体类属性名是否和数据库字段匹配，比如SQL中的关键字Type需要确保实体类已做对应映射；二是最终返回的匿名类属性命名是否和你接收时使用的名称一致。

内容的提问来源于stack exchange，提问作者Mik AA