使用相同的筛选器选择多个子列

本文关键字：选择 筛选 | 更新日期: 2023-09-27 18:15:16

我有这个Linq lambda表达式，它生成异常复杂的SQL选择数据库。有没有可能化简它?

var devices = db.Devices
                        .Where(a => a.active == true)
                        .Select(a => new DeviceToDisplay
                        {
                            Id = a.Id,
                            serialNumber = a.serialNumber,                               
                            deviceRegion = a.deviceRegion,
                            activeIP = a.IPaddresses.Where(b => b.active == true).Select(b => b.IPaddress1).FirstOrDefault(),
                            Wip = a.IPaddresses.Where(b => b.active == true).Select(b => b.W_IP).FirstOrDefault(),
                            Sip = a.IPaddresses.Where(b => b.active == true).Select(b => b.S_IP).FirstOrDefault(),
                            model = a.SPdatas.Where(c => c.model != "").OrderByDescending(c => c.collectionDate).Select(c => c.model).FirstOrDefault(),
                            firmware = a.SPdatas.Where(c => c.model != "").OrderByDescending(c => c.collectionDate).Select(c => c.firmware).FirstOrDefault(),                               
                            lastMPteamActivity = a.activityLogs.OrderByDescending(c => c.updatedDate).Select(c => c.updatedDate).FirstOrDefault(),                               
                            country = a.MPPinformations.Select(c => c.country).FirstOrDefault()                                
                        });

使用相同的筛选器选择多个子列

首先，您的linq查询看起来非常复杂。想象一下如何通过编写SQL查询来实现这一点。

一个建议:你正在写这样的东西:

 a.IPaddresses.Where(b => b.active == true).

和

  a.SPdatas.Where(c => c.model != "").OrderByDescending(c => c.collectionDate).

出现在多个位置。

相反，您可以创建一个匿名类型。例如 
var foo = from x in sb.Devices.Where(a=> a.active)
          select new { Id = x.ID, 
                       IPAddress = a.IPaddresses.Where(b => b.active), ... }

你可以使用foo来创建你的Devices对象。

看看这样是否更好:

var devices = db.Devices
  .Where(a => a.active == true)
  .Select(a => new DeviceToDisplay {
    Id = a.Id,
    serialNumber = a.serialNumber,                               
    deviceRegion = a.deviceRegion,
    activeIP = a.IPaddresses.Where(b => b.active == true).FirstOrDefault(),
    SPdata = a.SPdatas.Where(c => c.model != "").OrderByDescending(c => c.collectionDate).FirstOrDefault(),
    lastMPteamActivity = a.activityLogs.OrderByDescending(c => c.updatedDate).Select(c => c.updatedDate).FirstOrDefault(),                               
    country = a.MPPinformations.Select(c => c.country).FirstOrDefault()                                
})
  .Select(a=> new DeviceToDisplay {
    Id=a.Id,
    serialNumber=a.serialNumber,
    deviceRegion=a.deviceRegion,
    activeIP=a.activeIP.IPaddress1,
    Wip=a.activeIP.W_IP,
    Sip=a.activeIP.S_IP,
    model=a.SPdata.model,
    firmware=a.SPdata.firmware,
    lastMPteamActivity=a.lastMPteamActivity,
    country=a.county
});
相关文章：