Sudheer Kumar

1. Error Message : Only parameterless constructors and initializers are supported in LINQ to Entities:

 

The following query works perfetcly with LINQ:
NoticeRecipient rec = dc.NoticeRecipients.Where(o => o.RecipientID == new Guid(id)).FirstOrDefault<NoticeRecipient>();

 

But the same query will give the above mentioned error with Entity Framework.
The issue was using new Guid() inside lambda expressions. You have to change the query as follows.

 

 

So take a note of C# expressions, while writing queries for EF.

 
See the list of methods that can be used in an expression as of now:
http://msdn.microsoft.com/en-us/library/bb738681.aspx

 

a) You have the primary key on the table on left part of the Join

 

eg: You want to left outer join between Entity table and PostalAddress table.
Here PK is on Entity table.

            var v = from en in context.Entity
                    let leftouter = (from pa in en.PostalAddress
                                     select new
                                     {
                                         pa.PostalAddressID
                                     }).FirstOrDefault()
                    select new
                    {
                        EntityID = en.EntityID,
                        PostalAddressID = (Guid?)leftouter.PostalAddressID,
                    };

 

 

b) You have the primary key on the table on the right part of the Join:

eg:You want to do left outer join b/w NoitceRecipient table and NoitceStatus table. Here NoticeRecipient contains a FK and NoticeStatus has the PK.

        var v = from nr in context.NoticeRecipient
                    let lo1 = (from ns in context.NoticeStatus
                                     where ns.NoticeStatusID == nr.NoticeStatus.NoticeStatusID
                                      select new { ns.StatusText }).FirstOrDefault()
                    select new {nr.NoticeRecipientID, lo1.StatusText};
                    

 

Include will eager load all the columns in the table mentioned after doing a "LEFT OUTER" join based on the key fields relationship between 2 tables.

Note that it is not an INNER JOIN but causes a LEFT OUTER JOIN.
But if you do GROUP BY in your query, the Inlcude() option will be ignored.
eg: Get Entity and PostalAddresses using Include

var v = from en in context.Entity.Include("PostalAddress")
            select new {en.EntityID, en.PostalAddress.PostalAddressID}
 
NOTE: Through Include(), you are not including a table, but only one of the relationships of a table.
See this:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/9f8e82c8-e3a6-46d2-b6b3-e8338b46fb57


4. EF cannot do left outer join if there is no relationship:
Not very common, but there are cases where you might choose to store data w/o a relationship.
EF can't do a Left Outer Join in that case (neither include or let will work)
You can use LINQ-SQL in those scenarios.

EF cannot JOIN with a List<T> or doesn't support using : Contains() as can be done in LINQ-SQL.

Alternative us to build an lambda expression equivalent to IN.

eg:

 var grpNoticeRecipients = (from nr in ctx.NoticeRecipient.Where(
                                             EntityFrameworkUtils.BuildContainsExpression<NoticeRecipient, Guid>(e =>  e.NoticeRecipientID, noticeRecipientIDList))
                                       where nr.EmailAddress.EmailAddressID != null
                                      select nr).GroupBy(o => o.EmailAddress.EmailAddressID);
See more details on this post:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0

Note that Include() doesn't work with GroupBy

 

 

 

Also see this excellent blog.