Geeks With Blogs
Sudheer Kumar ASP.Net, C#, BizTalk, MSBuild, WPF, WCF, WF....
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: 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};
                    
 
3. Include("TableRelation"):
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.
5. 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

6. GROUP BY:
Note that Include() doesn't work with GroupBy
 
 
 
Also see this excellent blog.
 
Posted on Thursday, June 11, 2009 1:22 PM Entity Framework | Back to top


Comments on this post: Entity Framework Tips

# re: Entity Framework Tips
Requesting Gravatar...
Sudheer,

Nice little collection of tips

PS: Thanks for the link!

- Alex
Left by Alex James on Jun 12, 2009 11:54 AM

# re: Entity Framework Tips
Requesting Gravatar...
Reg . 6. GROUP BY:
Note that Include() doesn't work with GroupBy

There is a hack wherein you can do do a AsEnumerable just before the Groupby Clause., This will ensure that the data is materialized and therefore will be retained in the grouping
Left by SR on Sep 21, 2009 5:09 AM

# re: Entity Framework Tips
Requesting Gravatar...
Oh no! Include() doesn't work with GroupBy!

I have to do a reporting which is simple if Include supports GroupBy! What is the solution please?
Left by Rijaniaina on May 31, 2010 7:14 AM

# re: Entity Framework Tips
Requesting Gravatar...
Try using an explicit join instead of include.
Left by Sudheer on May 31, 2010 8:21 AM

# re: Entity Framework Tips
Requesting Gravatar...
All right/
Left by ice supplier on Mar 31, 2011 3:53 AM

Your comment:
 (will show your gravatar)
 


Copyright © Sudheer Kumar | Powered by: GeeksWithBlogs.net | Join free