c# - 如何逐步构建 LINQ => SQL/entities 查询(带连接)？

Question

我有以下两个 LINQ 查询:

public int getJobsCount()
{
    var numJobs =
        (from j in dbConnection.jobs
         join i in dbConnection.industries on j.industryId equals i.id
         join c in dbConnection.cities on j.cityId equals c.id
         join s in dbConnection.states on j.stateId equals s.id
         join pt in dbConnection.positionTypes on j.positionTypeId equals pt.id
         select j).Count();
    return numJobs;
}

public List<Job> getJobs()
{
    var jobs =
        (
            from j in dbConnection.jobs
            join i in dbConnection.industries on j.industryId equals i.id
            join c in dbConnection.cities on j.cityId equals c.id
            join s in dbConnection.states on j.stateId equals s.id
            join pt in dbConnection.positionTypes on j.positionTypeId equals pt.id
            orderby j.issueDatetime descending
            select new Job { x = j.field, y = c.field, etc }
        ).Skip(startJob - 1).Take(numJobs);
    return jobs;
} 

那里有很多重复的代码 - “from”和“join”行是相同的，我将添加一些“where”行，它们也将是相同的。

我尝试添加一个为第一部分返回 IQueryable 的方法:

public IQueryable getJobsQuery()
{
    var q =
        from j in dbConnection.jobs
         join i in dbConnection.industries on j.industryId equals i.id
         join c in dbConnection.cities on j.cityId equals c.id
         join s in dbConnection.states on j.stateId equals s.id
         join pt in dbConnection.positionTypes on j.positionTypeId equals pt.id;
    return q;
}

...但我得到“查询主体必须以 select 子句或组子句结尾”。

如果我在该函数的末尾添加一个 select 子句，我将无法对结果调用 count():

// getJobsQuery:
var q = from j in dbConnection.jobs
        join i in dbConnection.industries on j.industryId equals i.id
        join c in dbConnection.cities on j.cityId equals c.id
        join s in dbConnection.states on j.stateId equals s.id
        join pt in dbConnection.positionTypes on j.positionTypeId equals pt.id
        select new { a = j.y, b = c.z }

// another method:
var q = getJobsQuery();
var numJobs = q.Count(); // "IQueryable doesn't contain a definition for count"

有没有一种方法可以逐步构建此查询以避免重复大量代码？

Answer 1

有两种编写 LINQ 查询的方法，虽然使用哪种方法并不重要，但了解这两种方法是件好事，因为它们可能会让您了解 LINQ 的工作原理。

例如，您有一组作业。如果您要选择 industryId 为 5 的所有工作(随意猜测数据类型)，您可能会这样写:

from j in dbConnection.jobs
where j.inustryId == 5
select j;

同样的查询也可以这样写

dbConnections.jobs.Where(j => j.industryId == 5);

现在，我不是在这里宣扬一种方法比另一种方法更好，但在这里您可以清楚地看到使用扩展方法语法的 LINQ 如何自动在迭代对象上进行选择(除非您进行选择)，而在查询语法中，您必须明确地执行此操作。此外，如果您要在此处添加 inn 另一个 where 子句，它将看起来像这样:

from j in dbConnection.jobs
where j.inustryId == 5 // not using && here just to prove a point
where j.cityId == 3 // I THINK this is valid syntax, I don't really use the query-syntax in linq
select j;

在扩展方法中，您可以像这样附加更多方法调用:

dbConnections.jobs.Where(j => j.industryId == 5)
     .Where(j => j.cityId == 3);

现在很高兴知道这一点，因为这意味着您只需将 linq 查询放入一个函数中，然后继续查询它。为了使其适用于您的情况，您需要做的就是明确选择起始变量 j，或者您需要的所有变量，如下所示:

var q =
    from j in dbConnection.jobs
     join i in dbConnection.industries on j.industryId equals i.id
     join c in dbConnection.cities on j.cityId equals c.id
     join s in dbConnection.states on j.stateId equals s.id
     join pt in dbConnection.positionTypes on j.positionTypeId equals pt.id;
             select new {j = j, i = i, c = c, s = s, pt = pt };
return q;

那么你应该可以这样做:

 getJobsQuery().Where(a => a.i.id == 5); // I used a as a name for "all", like the collection of variables

或使用查询语法

 from a in getJobsQuery()
 where a.i.id == 5
 select a;