LinQ Join with nullable parameter

c# entity-framework entity-framework-core linq

Accepted Answer

When I need to perform a Join on a nullable field, I do this.

Initially Linq:

var projectDetails =
    from p in context.Project
    join u in context.User
        on p.AssignedUserID equals u.UserID into lj
    from x in lj.DefaultIfEmpty()
        select new {
            ProjectID = p.ProjectID,
            ProjectName = p.Name,
            UserLastName = u.LastName,
            UserFirstName = u.FirstName
        }

Adapted Linq:

var projectDetails =
    from p in context.Project
    join u in context.User
        on new {User = p.AssignedUserID} equals new {User = (int?)u.UserID} into lj
    from x in lj.DefaultIfEmpty()
        select new {
            ProjectID = p.ProjectID,
            ProjectName = p.Name,
            UserLastName = x.LastName,
            UserFirstName = x.FirstName
        }

Casting the UserId's int to a nullable int will resolve your problem because you are trying to connect an int with an int?, which is causing the error message.

10
3/10/2015 1:24:29 AM

Popular Answer

Many of the tasks that EF is supposed to complete for you are really carried out by you (this frequently occurs, especially withjoin ).

A navigation attribute is necessary for yourProject class, not simply the FK you now possess:

The following POCOs were changed for EF Database-First (i.e.partial classes).

// generated code
public partial class Project
{
    public int ProjectId { get; set; }
    public string Name { get; set; }
    public int? AssignedUserId { get; set; }
}

public class User
{
    public int UserId { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
}

// your code
public partial class Project
{
    [ForeignKey("AssignedUserId")]
    public User User { get; set; }
}

Keep in mind that you must remember to include thepartial in the produced version of theProject however, this is still preferable since by keeping your additions in a different class each time you regenerate it,partial declaration of the class:partial The only item you'll need to remember to modify in the created class is keyword.

So, the answer to your question is as follows:

var projectDetails = from p in context.Projects
                     select new
                     {
                         p.ProjectId,
                         p.Name,
                         p.User.LastName,
                         p.User.FirstName
                     };

Check out how this appears:

Console.WriteLine(projectDetails.ToString());

This results in the SQL query:

SELECT
    [Extent1].[ProjectId] AS [ProjectId],
    [Extent1].[Name] AS [Name],
    [Extent2].[LastName] AS [LastName],
    [Extent2].[FirstName] AS [FirstName]
    FROM [Projects] AS [Extent1]
    LEFT OUTER JOIN [Users] AS [Extent2] ON [Extent1].[AssignedUserId] = [Extent2].[UserId]

which seems to be just what you desire.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow