Simons SQL Blog : Entity framework

Entity framework support for table valued functions and thus full text

simonsabin — Sun, 30 Jan 2011 14:12:03 GMT

One of my most popular posts with over 10, 000 hits is how to enable full text when using LINQ to SQL http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx , core to this is the use of a table valued function...(read more)

ORMs - Should DBAs just lighten up?

simonsabin — Fri, 12 Mar 2010 23:27:12 GMT

I did a presentation at DDD8 on the entity framework and how to stop your DBA from having a heart attack. You can find my demos and slide deck here http://sqlblogcasts.com/blogs/simons/archive/2010/01/30/Entity-Framework-how-to-stop-your-DBA-having-a-heart-attack.aspx

Whilst at DDD Mike Ormond interviewed me about my view on ORMs and the battel between DBAs and Devs. To see what I said go tohttp://bit.ly/bnf1By

DDD 8 Entity framework follow up - Will cast(column as int) allow use of an index

simonsabin — Sun, 31 Jan 2010 18:53:42 GMT

I pointed out yesterday that the EntityFramework doesn't like smallints. Well it does and it doesn't The model handles them fine and the objects have datatypes of int16 which is great.

However the queries that are generated do this odd thing with any predicates on such columns. It applies a cast to column.i.e

where cast(col2 as int)= @d

At first glance I thought oh now not another screw up with the SQL from Entity framework, but on inspection of a query plan where the column in question was indexed I still got a seek operation. Very nice, well done optimisation team.

When I pointed this out, I was asked whether this worked on SQL2005. Having only got a SQL2008 instance on my laptop I took the question away for testing. The reason of the question could be related to the fact that in SQL 2008 the introduced the ability to cast a datetime to a date to be able to look for specific days of data and still use an index.

I have now tested and can state that it does work on SQL 2005.

Entity Framework how to stop your DBA having a heart attack

simonsabin — Sat, 30 Jan 2010 14:30:32 GMT

My code and presentation from my session at Developer Day 8 are now available here

DDD_8-Entity_Framework Code
Entity framework presentation

Any questions let me know.

Also for more on the new features in Entity Framework v next go to http://blogs.msdn.com/adonet/archive/2009/08/05/improvements-to-the-generated-sql-in-net-4-0-beta1.aspx

Entity Framework - MergeOption.NoTracking and CompiledQuery.Compile

simonsabin — Sat, 02 Jan 2010 15:52:20 GMT

Two things that people tell you about Entity framework performance are, use the Compiled Queryies and potentially remove tracking when its not needed. You can achieve this by using the CompiledQuery.Compile function to obtain a function that is your compiled query and to set the MergeOption of the relevant entities on the ObjectContext.

I've just finished banging my head against a wall when trying to assess the performance of these together.

This was my simple code. Create a static readonly function that is the compiled query and then in by test function checking the status of a check box to set the MergeOption to MergeOption.NoTracking.

static readonly Func<MobileMinderEntities, int, IQueryable<Job>> JobSelect

= CompiledQuery.Compile<MobileMinderEntities, int, IQueryable<Job>>

((ctx, jobid) => from j in ctx.Job

where j.Person.PersonId == jobid

&& ctx.JobCompleted.Any(c => c.Signature == null)

select j);

private void btnTest_Click(object sender, EventArgs e)

{

int var = 1;

using (MobileMinderEntities me = new MobileMinderEntities())

{

if (!chkTrack.Checked)

{

me.Job.MergeOption = MergeOption.NoTracking;

}

var l = JobSelect2(me, var);

OutputTraceQuery(l);

var l_l = l.ToList();

}

I couldn't figure out why I wasn't getting much difference, so I started profiling the code to see what was going on. Not much there (but I'm not a profiling expert). I then started hacking code to debug what was going on, what I couldn't figure was that when I turned tracking on, my object context was reporting no tracked objects. Uhh.

Can you spot whats wrong?

After making a small dent in the wall I figured the only way that this was happening was if the traking was being cached. Initially I thought it was the object context but then thought that its disposed every time, its not a singleton. So then looked at the function, ok so this is a static readonly, so only one but surely only the execution tree is cached.

Well it appears not. It appears that the compiled query also includes the tracking options.

That sort of put my test in a difficult position.The solution I came up with was to use two functions one that would only be used for tracking and one for non tracked execution. This worked a treat.

So a word to the wise if you are using CompiledQueries or ObjectQuery.Compile be careful if you are setting the mergeoption on your ObjectContext

Whats the score with LINQ to SQL?

simonsabin — Sun, 16 Nov 2008 00:16:48 GMT

LINQ to SQL (and any ORM for that matter) has the marmite factor. You either love it or hate it. Whats more many of those that hate it haven't tried it. In many cases the hate it crowd are making a decision based on previous experience with systems using ORM solutions.

I've been looking at and trying to use LINQ to SQL for a while and thought I would post my findings thoughts. Before starting this series of posts I thought I would see what others have said. I had heard something about an announcement about LINQ to SQL and LINQ to Entities and found the post hear. http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx

I made a conscious decision to look at L2S rather that L2E for these reasons,

1. Its simpler

2. It allows separation of code and database mapping easily. This is commonly referred to as POCO

3. It supports more SQL features

However it seems that L2E will be enhanced to include the L2S features that are missing.

Thats a real shame and the biggest reason for me is the number of levels of interpretation.

When you right a LINQ query with L2S your LINQ query is translated to an expression tree and then translated to SQL.

With L2E you have an extra step because the entity framework is a framework that sits on stop of any provider (that has been written). To enable that it has its own data access language Entity SQL. So to get to SQL, your LINQ query is translated from LINQ to ESQL to SQL.

Anyone thats worked or seen translated text will know that often what you put in is often not what you get out. There is a common games on radio in the UK where they translate an album/track name to a language and then back to English. The quiz is then to guess what the original album/track name was given the final translated text. (A bit like Chinese whispers).

We all know how complex SQL is, so you can understand why generally the SQL you get from L2E is more complex than with L2S.

I personally like to keep things simple and so I’m disappointed by the decision to concentrate. Although all is not lost.

If you use standard practice of implementing a data layer then you could move to L2E in the future. Whats more your SELECT LINQ queries against a set of L2E objects should be similar to those for L2S. The main difference is when you want to do your insert, update and delete operations, the two technologies use different mechanisms. Further more you will likely have to change you validation code to use the different technology, although the core rules code shouldn’t have to change.

I would still stick with L2S until the next version of EF. I'll discuss some of my findings in future posts.