Entity Framework + LINQ Expression outer join error

Question

i want to create a left outer join for a linq expression that query data from database via entity framework. this is the linq expression. basically what I am trying to do is search problem_vehicle_id from problemVehiclesTicket in Problems table to see if it exists, if it doesn't exists, i want to return a problem object that is null/empty. Basically I believe it is left outer join.

   var ticketsDetails = (from tickets in DbContext.tickets
                                       join problemVehiclesTicket in DbContext.problem_vehicle on tickets.tickets_id equals problemVehiclesTicket.tickets_id
                                       join problems in DbContext.problem on problemVehiclesTicket.problem_vehicle_id equals problem.problem_vehicle_id into problemGroup
                                       from problems in problemGroup.DefaultIfEmpty(new problem { })
                                       where (tickets.tickets_id == ticketsId)
                                       select new TicketsDetails
                                       {
                                           Ticket = tickets,
                                           ProblemVehicle = problemVehiclesTicket,
                                           Problems= problem,
                                       }).ToList();

Problem is a class that mirrors that of the Problem table in database

`Problem`
id (int), description (string), type (short)

The error i got is "The entity or complex type 'SPOTS_Repository.speeding_offence' cannot be constructed in a LINQ to Entities query." The source is from Entity Framework.

any help is greatly appreciated.

The easiest (and standard) way is to remove `new problem { }` from `DefaultIfEmpty` call, i.e. use the parameterless overload `DefaultIfEmpty()` which will give you a `null` problem instance in case there is no match. — Ivan Stoev, May 24 '17 at 08:50

CodingYoshi · Accepted Answer · 2017-05-25T23:22:34.083

The type problem in your case is a mapped entity. Therefore, you cannot project onto it. You can use an anonymous type or another non-mapped class (DTO).

Because in your DefaultIfEmpty method you are constructing a new problem, which is a mapped entity, this not allowed.

Fix

You do not need to pass anything to DefaultIfEmpty method. Actually in your case, you are not even allowed because the only thing you can pass is problem and that is mapped. Therefore, use .DefaultIfEmpty() without creating a new problem.

More Belabor

Here is an example, which will clarify the usage of DefaultIfEmpty:

Option 1: DefaultIfEmpty() with No Parameter

var list1 = new List<int> { 1, 2, 3, 6, 4 };
var list2 = new List<int> { 4, 1, 2 };
var selection =
    from l1 in list1
    join l2 in list2 on l1 equals l2 into joined
    from j in joined.DefaultIfEmpty()
    select j;

Output: 1, 2, 0, 0, 4 Why? Because 3 and 6 are not found and DefaultIfEmpty for an integer returns a 0.

Option 2: DefaultIfEmpty() with Parameter

In some cases we may want to indicate that if the item is not found in the join, what to return instead. We can do that by sending a single parameter to DefaultIfEmpty method like this:

var list1 = new List<int> { 1, 2, 3, 6, 4 };
var list2 = new List<int> { 4, 1, 2 };
var selection =
    from l1 in list1
    join l2 in list2 on l1 equals l2 into joined
    from j in joined.DefaultIfEmpty(99) //<-- see this
    select j;

Output: 1, 2, 99, 99, 4 Why? Because 3 and 6 are not found and we instructed DefaultIfEmpty to return a 99 in that case.

Please note that DefaultIfEmpty is a generic method. In my case it required an int because I am joining to the second list which is a List of int(s). In your case it is problem(s) but that is mapped. Therefore, you cannot construct it in your query.

Here is another example:

var depts = new List<Department>
{
    new Department { Name = "Accounting" },
    new Department { Name = "IT" },
    new Department { Name = "Marketing" }
};
var persons = new List<Person>
{
    new Person { DeptName = "Accounting", Name = "Bob" }
};

var selection2 =
    from d in depts
    join p in persons on d.Name equals p.DeptName into joined2
    // See here DefaultIfEmpty can be passed a Person
    from j2 in joined2.DefaultIfEmpty(new Person { DeptName = "Unknown", Name = "Alien" })
    select j2;

foreach(var thisJ in selection2)
{
    Console.WriteLine("Dept: {0}, Name: {1}", thisJ.DeptName, thisJ.Name);
}

Output:

Dept: Accounting, Name: Bob
Dept: Unknown, Name: Alien
Dept: Unknown, Name: Alien

<== Fiddle Me ==>

sorry, i dont get what you are trying to do here. are you trying to instantiate an anonymous type? — user1166085, May 24 '17 at 03:35
Yes instead of instantiating your mapped entity class, use an anonymous type. See [this](https://stackoverflow.com/questions/25304665/linq-with-defaultifempty-with-select-new) for more details. — CodingYoshi, May 24 '17 at 03:45
does that mean if my Problem has 10 variables, i need to do for 10 of them? — user1166085, May 24 '17 at 03:57

score 0 · Answer 2 · answered May 24 '17 at 11:49

0

Public class problem()
{
public int id;
public string description;
public short type;
}

.DefaultIfEmpty(
    new problem()
    {
        Id = ticketsId,
        Description = string.empty,
    });

create class and make use of that in linq query Hope it helps you.

answered May 24 '17 at 11:49

Vinutha N

156
6

that's a great freaking explaination. thank you so much – user1166085 May 29 '17 at 12:40

Entity Framework + LINQ Expression outer join error

2 Answers2