CODEWITHSUNDEEP
c#mysqlentity-frameworklinqleft-join
user1166085
user1166085

Reputation: 627

Entity Framework + LINQ Expression outer join error

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.

Upvotes: 0

Views: 905

Answers (2)

CodingYoshi
CodingYoshi

Reputation: 27009

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 ==>

Upvotes: 1

Vinutha N
Vinutha N

Reputation: 166

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.

Upvotes: 0

Related Questions