How do I build up a LINQ => SQL / entities query (with joins) step-by-step?

Question

I have the following two LINQ queries:

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

There's a lot of duplicate code in there - the "from", and "join" lines are identical, and I'll be adding in some "where" lines as well that will also be identical.

I tried adding a method that returned an IQueryable for the first part:

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

...but I get "a query body must end with a select clause or a group clause".

If I add a select clause on to the end off that function, I can't call count() on the result:

// 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"

Is there a way to build up this query step-by-step to avoid duplicating a whole lot of code?

Oh, and the reason you can't use Count is probably cause you lack the System.Linq namespace include. — Alxandr, May 26 '11 at 07:10

score 3 · Accepted Answer · answered May 26 '11 at 07:07

There are two ways of writing LINQ-queries, and though it doesn't really matter witch one you use it's good to know both of them cause they might learn you something about how LINQ works.

For instance, you have a set of jobs. If you were to select all jobs with an industryId of 5 (wild guess of data-types) you'd probably write something like this:

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

The very same query can also be written like this

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

Now, I'm not here to preach saying one way is better than the other, but here you can clearly see how LINQ using the extension-methods syntax automatically selects on the iterated object (unless you do a select), whereas in the query-syntax you must do this explicitly. Also, if you were to add inn another where clause here it would look something like this:

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;

While in the extension-methods you can just append more method-calls like so:

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

Now this is good to know cause this means you can just put your linq-query inside a function an continue querying it. And all you need to do to make it work in your case is just explicitly select the starting variable j, or all the variables you need like so:

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;

Then you should be able to do for instance this:

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

or using the query-syntax

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

You have an extra ; right before `select new {j = j, ...`. Otherwise, good stuff. — Justin Morgan - On strike, May 26 '11 at 17:44

score 0 · Answer 2 · answered May 26 '11 at 06:51

Would this be better solved by returning a set of data (e.g. the common data) and querying for a subset of that data?

E.g. [pseudocode]

var allJobs =
    (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);

var myJobs = allJobs.OrderBy(j => j.issuedate).skip(expr).Take(allJobs.Count);

or similar...

Later on I'm going to want to run some `where` clauses against things in tables other than "cities" - and it doesn't look like I'll be able to do that if my first query is just selecting "jobs" records? — George, May 26 '11 at 07:08

How do I build up a LINQ => SQL / entities query (with joins) step-by-step?

2 Answers2