LinQ Join with nullable parameter

c# entity-framework entity-framework-core linq

Question

I can't seem to get the hang of the following problem with EntityFramework Core and solve it

using (var db = new Context(configuration))
{
   var myQuery = (
                 from tabA in db.TABA
                 join tabAB in db.TABB on tabA.NullableField equals tabAB.PrimaryKey
                 select ...
                 ).ToList();

}

I have more joins which work , but if this join is executed, my query becomes null. So, no result is getting generated. Can someone pinpoint me how to solve this issue? It is a Database-First approach.

1
0
2/21/2019 9:12:40 PM

Accepted Answer

This is what I do when I have to do a Join on a nullable field

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

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

The problem is that you are trying to join an int with an int?, which is giving you the error message, cast the int of the UserId to a nullable int, will solve your issue.

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

Popular Answer

You're actually doing a lot of the work that EF is supposed to be doing for you (this happens alot, in particular with join).

You need a navigation property on your Project class, not just the FK you have now:

EDIT: updated the following POCOs for EF Database-First (i.e. using 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; }
}

Note that you have to remember to add the partial keyword on the generated version of the Project class each time you regenerate it, but this is still better, because — by keeping your additions in a separate partial class declaration — the partial keyword is the only thing you'll have to remember to change in the generated class.

So your query is simply the following:

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

Let's see what this looks like:

Console.WriteLine(projectDetails.ToString());

This yields the following SQL:

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 would appear to be exactly what you want.



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