LINQ LEFT JOIN on Nullable

c# entity-framework join linq

Question

Below are two tables:

Project(
    ProjectID       INT,
    Name            VARCHAR(200),
    AssignedUserID  INT NULL
)
User(
    UserID      INT,
    LastName    VARCHAR(50),
    FirstName   VARCHAR(50)
)

Entity Framework database-first method is what I'm using. In my model, then:

Class Project{
    public int ProjectID;
    public string Name;
    public Nullable<int> AssignedUserID;
}

Class User{
    public int UserID;
    public string LastName;
    public string FirstName;
}

I want to ask everyonePROJECT its designated user, and

SELECT
    p.ProjectID,
    p.Name,
    u.LastName,
    u.FirstName
FROM Project p
LEFT JOIN Users u ON u.UserID = p.AssignedUserID

Linq translation:

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
        }

But I'm getting a mistake:

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

I've tried both fixes for int? when LEFT OUTER JOIN in the Linq problem, and int comparison, but these issues persist:

Using the answer:(int)(p.AssignedUserID ?? default(int))

LINQ to Entities does not recognize the method 'Int32 ToInt32(Int32)' method, and this method cannot be translated into a store expression.

Using the answerGetValueOrDefault() :

LINQ to Entities does not recognize the method 'Int32 GetValueOrDefault(Int32)' method, and this method cannot be translated into a store expression.

How are you?LEFT JOIN on Nullable<int> and int ?

1
6
5/23/2017 12:09:42 PM

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