Entity Framework Tips

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.

Guid gID = new Guid(id);

NoticeRecipient rec = dc.NoticeRecipients.Where(o => o.RecipientID == gID).FirstOrDefault<NoticeRecipient>();

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

2. Left-Outer Join – 2 Styles:

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

eg:

var v = from en in context.Entity
    where en.EntityID == new Guid("98F89C0F-7311-42EC-B266-000A738BE8A5")
                    let leftouter = (from pa in en.PostalAddress
                                     select new
                                     {
                                         PA = pa,
                                         PAT = pa.PostalAddressType
                                     }).FirstOrDefault()
                    select new
                    {
                        EntityID = en.EntityID,
                        PostalAddressID = (Guid?)leftouter.PA.PostalAddressID,
                        PostalTypeID = (Guid?)leftouter.PAT.PostalAddressTypeID
                    };

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

eg:

var v = from nr in context.NoticeRecipient
                    join n in context.Notice on nr.Notice.NoticeID equals n.NoticeID
                    where nr.NoticeRecipientID == new Guid("CE364829-8D94-4809-84F9-01CC505E5BEA")
                    let lo1 = (from ns in context.NoticeStatus where ns.NoticeStatusID == nr.NoticeStatus.NoticeStatusID select new { NS = ns }).FirstOrDefault()
                    select new { lo1.NS.NoticeStatusID};

3. Include(“Table”):

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.

3. Implementing SQL IN:

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

4. GROUP BY:

Note that Include() doesn’t work with GroupBy

Also see this excellent blog.

http://blogs.msdn.com/alexj/archive/2009/03/26/index-of-tips.aspx