实体框架多个where条件，寻找正确性或更好的方法

Question

我正在尝试从我的数据库加载客户对象，其中客户的名称可以包含特定字符串以及在特定字段中具有特定值的任何关联地址。我已经做了很多研究等来将这些放在一起。这是构建此类查询的正确方法吗？如果不是，“更正确”的方法或更有效的方法是什么？感谢您抽出时间。

我拥有 LINQPad，这个查询是使用它编写的。

var customerName = "J";
var street = "Rd";
var city = "asdf";
var state = "TN";
var zip = "27613";

var query = (
    from c in customers
        .Include("locations")
        .Include("locations.address")
    where
        c.name.Contains(customerName) ||
        c.locations.Any(l => l.address.street1.Contains(street)) ||
        c.locations.Any(l => l.address.street2.Contains(street)) ||
        c.locations.Any(l => l.address.city.Contains(city)) ||
        c.locations.Any(l => l.address.state.Contains(state)) ||
        c.locations.Any(l => l.address.zip.Contains(zip))
    select new
    {
        c.id,
        c.name,
        c.locationId,
        c.location,
        Locations = 
            from l in c.locations 
            where 
                l.address.street1.Contains(street) || 
                l.address.street2.Contains(street) || 
                l.address.city.Contains(city) ||
                l.address.state.Contains(state) ||
                l.address.zip.Contains(zip)
            select new
            {
                l.id,
                l.address,
                l.description,
                l.locationType
            }
    }
);

query.Dump();

这确实给了我想要的结果，但我想确保我以对数据库最礼貌的最佳方式执行此操作。

我确信这是使用实体框架进行搜索的非常典型的模式。

Answer 1

我不会把这个大谓词写两次，而是把它翻转过来。另外，当您投影时，Include 是完全没有必要的。

var query = 
    from l in c.locations
    where 
        l.customer.name.Contains(customerName) ||
        l.address.street1.Contains(street) || 
        l.address.street2.Contains(street) || 
        l.address.city.Contains(city) ||
        l.address.state.Contains(state) ||
        l.address.zip.Contains(zip)
    group l by l.customer into g
    select new
    {
        id = g.Key.id,
        name = g.Key.name,
        // etc.
        Locations = from l2 in g
                    select new
                    {
                        l2.id,
                        l2.address,
                        l2.description,
                        l2.locationType
                    }
    };