Linq query with first or default join

Question

I have the following data model:

public class Course
{
    public int CourseId { get; set; }
    public int StateId { get; set; }
}

public class CompletedCourse
{
    public int CompletedCourseId { get; set; }
    public int UserId { get; set; }
    public Course Course { get; set; }
    public string LicenseNumber { get; set; }
}

public class License
{
    public int LicenseId { get; set; }
    public int UserId { get; set; }
    public int StateId { get; set; }
    public string LicenseNumber { get; set; } 
}

I'm trying to come up with an IQueryable for CompletedCourses and I would like to populate CompletedCourse.LicenseNumber with the LicenseNumber property of the FirstOrDefault() selection from my Licenses table where UserId and StateId match the completed course records.

Here is my query, but I don't think this will handle duplicate licenses correctly:

var entries =
    (from course in context.CompletedCourses
         join license in context.Licenses on course.UserId equals license.UserId
         where license.StateId == course.Course.StateId
         select course)
    .Include(x => x.Agent)
    .Include(x => x.Course.State);

Is this something that can be done in a single query? Thanks in advance.

In the `CompletedCourse` table, isn't there a `LicenseNumber` column? How does EF know not to create one since you have a property for it? — Yacoub Massad, Mar 15 '16 at 20:29
@YacoubMassad: It's more so needed by my ViewModel. In my EntityConfiguration for the CompletedCourse entity, I'm ignoring that property. — mellis481, Mar 15 '16 at 20:40

Ivan Stoev · Answer 1 · 2016-03-15T20:58:07.837

8

Here is how you can do that:

var entries =
    (from course in context.CompletedCourses
     join license in context.Licenses
     on new { course.UserId, course.Course.StateId }
     equals new { license.UserId, license.StateId }
     into licenses
     let licenseNumber = licenses.Select(license => license.LicenseNumber).FirstOrDefault()
     select new { course, licenseNumber });

But please note that with this type of projection you cannot have Includes in your query (you can, but they will not be in effect).

The EF generated query I'm getting from the above is:

SELECT 
    [Extent1].[CompletedCourseId] AS [CompletedCourseId], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[LicenseNumber] AS [LicenseNumber], 
    [Extent1].[Course_CourseId] AS [Course_CourseId], 
    (SELECT TOP (1) 
        [Extent2].[LicenseNumber] AS [LicenseNumber]
        FROM  [dbo].[Licenses] AS [Extent2]
        INNER JOIN [dbo].[Courses] AS [Extent3] ON [Extent3].[StateId] = [Extent2].[StateId]
        WHERE ([Extent1].[Course_CourseId] = [Extent3].[CourseId]) AND ([Extent1].[UserId] = [Extent2].[UserId])) AS [C1]
    FROM [dbo].[CompletedCourses] AS [Extent1]

It can be noticed that EF effectively ignores the join, so the same result can be obtained by simple natural query:

var entries =
    (from course in db.CompletedCourses
     let licenseNumber =
        (from license in db.Licenses
         where license.UserId == course.UserId && license.StateId == course.Course.StateId
         select license.LicenseNumber).FirstOrDefault()
     select new { course, licenseNumber });

edited Mar 15 '16 at 20:58

answered Mar 15 '16 at 20:16

Ivan Stoev

195,425
15
312
343

When I do that, I get the following compilation error where the word `join` has the red squiggly underline in Visual Studio: `The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.` It doesn't appear to like the `on` expression... – mellis481 Mar 15 '16 at 20:23
Also, includes are imperative. – mellis481 Mar 15 '16 at 20:24
@im1dermike (1) I had a typo (`course.StateId`) which is fixed. (2) They are, but do not work with anonymous projection – Ivan Stoev Mar 15 '16 at 20:31
If you still get the compilation error, then your actual model classes are different from the posted, because I've copied/pasted them in my EF test environment and the query compiles and executes w/o a problem. – Ivan Stoev Mar 15 '16 at 20:40
Is there a using library I need for this to work? System.Linq.Dynamic? – mellis481 Mar 15 '16 at 20:43
No, that's a standard LINQ. – Ivan Stoev Mar 15 '16 at 20:44
[How to: Join by Using Composite Keys (C# Programming Guide)](https://msdn.microsoft.com/en-us/library/bb907099.aspx) – Ivan Stoev Mar 15 '16 at 20:46
Indeed the model is slightly different. License's property is `UserId` and the issue was that the the anonymous type AND names weren't the same. See my post. – mellis481 Mar 16 '16 at 15:06
I saw it. So the conclusion is that it can't be done with single query because of the `Include` requirement. But, it's your query, so if the way you found works for you, it works for me - issue closed :) Happy coding! – Ivan Stoev Mar 16 '16 at 15:39
I did learn about joining on anonymous types so thanks again for that! – mellis481 Mar 16 '16 at 16:23

score 0 · Accepted Answer · answered Mar 16 '16 at 15:05

@IvanStoev's answer was very helpful in joining on anonymous types, but ultimately I couldn't use it because I needed Includes. Here is the solution I went with that results in two DB queries instead of one which is fine for my situation.

var entries = context.CompletedCourses
    .Include(x => x.Agent)
    .Include(x => x.Course);
var courses = entries.ToList();
var courseIds = entries.Select(x => x.CompletedCourseId);
var licenses =
    (from course in entries
        join license in context.Licenses
        on new { course.AgentId, course.Course.StateId } 
        equals new { AgentId = license.UserId, license.StateId }
        where courseIds.Contains(course.CompletedCourseId)
        select license);
foreach (var course in courses)
{
    var license = agentLicenses.FirstOrDefault(x => x.UserId == course.AgentId && 
        x.StateId == course.Course.StateId);
    if (license != null)
    {
        course.LicenseNumber = license.LicenseNumber;
    }
}
return courses;

Linq query with first or default join

2 Answers2