Left outer join in Linq To Entities (The entity or complex type cannot be constructed in a LINQ to Entities query.)

Question

I have two class that map two tables of my database:

public class Product
{
    public int Id { get; set; }
    public string Token { get; set; }
    public string Name { get; set; }
    public decimal Value { get; set; }
}

public class Ticket
{
    public int Id { get; set; }
    public string SerialNumber { get; set; }
    public string ProductToken { get; set; }
    public Product Product { get; set; }
}

For some domain reasons, Product and Ticket are logically linked, in other words, they are not linked in a database relationship that could be mapped by EF, they will be linked in my app with a linq query that "must" be translated in a SQL Outer Left Join. From this, i did the following query:

IQueryble<Ticket> query = from ts in context.Tickets
                          join ps in context.Products 
                               on ts.ProductToken equals ps.Token into p
                          select new Ticket
                          {
                              Id = t.Id,  
                              SerialNumber = t.SerialNumber,
                              ProductToken = t.ProductToken,
                  Goal -----> Product = p.FirstOrDefault()
                          };

The query keeps as IQueryble because after that, the query keeps to be refined with a filter.

The problem is when i run the following code:

var tickets = query.OrderBy(t => t.SerialNumber).ToList();

I got the following error:

"The entity or complex type 'Model.Ticket' cannot be constructed in a LINQ to Entities query."

So, how can i reach my goal?

Habib · Answer 1 · 2012-04-17T17:06:11.817

5

You cannot project the result to a mapped entity, either you could project it to a annonymous type or create your own Ticket type something like:

public class myTicket
{
    public int Id { get; set; }
    public string SerialNumber { get; set; }
    public string ProductToken { get; set; }
    public Product Product { get; set; }
}

and then:

IQueryble<myTicket> query = from ts in context.Tickets
                          join ps in context.Products 
                               on t.ProductToken equals p.Token into p
                          select new myTicket
                          {
                              Id = t.Id,  
                              SerialNumber = t.SerialNumber,
                              ProductToken = t.ProductToken,
                              Product = p.FirstOrDefault()
                          };

Also you need to use DefaultIfEmpty() for left outer join

edited Apr 17 '12 at 17:06

answered Apr 17 '12 at 16:59

Habib

219,104
29
407
436

Nice work, just a note you missed the order by Serial Number :) – Mathew Thompson Apr 17 '12 at 17:02
I didn't understand why i have to create a new class identical to the POCO class, and if you are creating a `new myTicket` in the select return, why the IQueryble keeps the same (Ticket), it will run, need some cast? – Vinicius Ottoni Apr 17 '12 at 17:04
@ViniciusOttoni, IQueryable was a typo, it should be myTicket there, the reason you can't project to mapped entity is because an entity is representing a database table. Using project you are selecting a part of that table (some columns) which is invalid state for data – Habib Apr 17 '12 at 17:07
There is no way to select part of the data and keeps doing another queries with the main query? – Vinicius Ottoni Apr 17 '12 at 17:11
@ViniciusOttoni, in your case I don't know if there is, I would love to know though – Habib Apr 17 '12 at 17:12

Left outer join in Linq To Entities (The entity or complex type cannot be constructed in a LINQ to Entities query.)

1 Answers1