Entity Framework LINQ query left join into new object instead of null

Question

I am basically trying to perform a left outer join in my LINQ query but I want to return an empty instance of the left-joined object instead of null. My solution below results in an error:

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

 public MyObjectsHolder GetObjectHolder()
     {
         using (MyEntities ctx = new MyEntities()) 
         {
             var query = (from a in ctx.tableA
                          join b in ctx.tableB on b.FKID equals a.PKID into b_a
                          from b in b_a.DefaultIfEmpty()
                          select new MyObjectsHolder()
                          {
                              SubObject1 = a,
                              SubObject2 = b ?? new SubObject2()
                          });

             return query.FirstOrDefault();
         }
     }

Corey Adler · Accepted Answer · 2014-12-10 15:33:09Z

2

The LINQ to Entities provider is throwing that because it has no way of properly translating that instantiation into a SQL query (which is what LINQ to Entities does). Since your method is only returning one object, I would recommend that you do the following to compensate:

var query = (from a in ctx.tableA
             join b in ctx.tableB on b.FKID equals a.PKID into b_a
             from b in b_a.DefaultIfEmpty()
             select new MyObjectsHolder()
             {
                 SubObject1 = a,
                 SubObject2 = b
             });

var result = query.FirstOrDefault();

if(result != null && result.SubObject2 == null) result.SubObject2 = new SubObject2();

return result;

edited Dec 10, 2014 at 15:33

answered Dec 10, 2014 at 15:07

Corey Adler

16.2k18 gold badges70 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework LINQ query left join into new object instead of null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related