Developers have been playing with LINQ to SQL long enough now that they are past the “how to” and thinking about performance. Jim Wooley blogs about LINQ to SQL Perf and using CompiledQuery.Compile to get the best performance. He also points to a series of posts on LINQ to SQL Performance by msdn blogger, Rico Mariani.

Of course, the next question for me is how LINQ to SQL will compare to LINQ to Entities and some of the other methods of extracting data via Entity Framework. While Entity Framework is still in earlier stages than LINQ to SQL and I’m guessing they are still working out perf, I did ask about this on the ADO.NET Orcas forums and was told that they’ll be working on writing something up about this.

It took me a while to figure out what caused this problem, so I thought I’d share it here.

I was trying to write a query in VB to grab customers who have orders placed after July 1, 2007.

The query looks like this:

Dim q = From cust In nwentities.Customers _
  Where (cust.Orders.Any(Function(o) _
     o.OrderDate > New DateTime(2007, 7, 1)))

But it would not compile. I have Option Strict On and the error was

Option Strict On disallows implicit conversions from ‘Boolean?’ and ‘Boolean’.

What the heck was this “Boolean?”. Not google-able, that’s for sure!

Then I noticed that OrderDate was being defined as a “Date?”.

What I’m seeing is the shortcut for Nullables in VB. It’s really hard to google for that. But if you look it’s everywhere! Such as in this post by Bill McCarthy. (See Bill? I found it all by myself! :-))

OrderDate is nullable. “Date?” means nullable date.

And the VB’s compiler is casting the entire expression to a nullable Boolean then telling me “Yo! A Nullable boolean is NOT the same as a boolean! Sorry”.

Note that the designer showed Nullable as false but the actual database has Nullable=true.

So, while I can write a regular query with this model, such as:

and I can write my exact query in C# with no worries:

   var q=from cust in nw.Customers where cust.Orders.Any(o=>o.OrderDate>new DateTime(2007,7,1)) select cust;

I believe that in my scenario, I just need to rethink my query. But later…

The new Entity Framework tools create an EDMX file which contains all three models: Conceptual, Mapping and Storage, in one file. When your project is built, individual files are created for the models, in the [familiar to those who have been using Entity Framework already] CSDL (conceptual), MSL (mapping) and SSDL (storage) files.

The connection string used by the Entity Framework contains not only the database connection string, but a metadata parameter with pointers to the three mapping files separated by a pipe character.

The Default Locations

Assuming that you put your EDM in the main folder of your project, in Windows Forms, Console and Class Library projects, you will see by default:

connectionString=
“metadata=.\Model.csdl|.\Model.ssdl|.\Model.msl;provider=System.Data.SqlClient;provider connection string=’Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True'”

When the project is built, those 3 files will land in the debug or release folder of the BIN directory for the app.

In a web app, the default location is different:

connectionString=
“metadata=~/bin/Model1.csdl|~/bin/Model1.ssdl|~/bin/Model1.msl;provider=System.Data.SqlClient;provider connection string=’Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True'”

This pushes the files into the bin folder of the site as well.

Sharing EDM with another project or another developer

If you have your EDM in it’s own project and then want to reference it from another project, you need to perform two steps.

1) Add a reference to the compiled DLL which represents the EDM.
2) Modify the config file of your consuming application to point to the location of the csdl, msl and ssdl files.

Here is an example of  a web.config’s connection string at design time that needs to use a model which is in a different project folder. I had to manually enter this long file path. Yucch.

connectionString=”metadata=C:\Documents and Settings\julie\My Documents\Visual Studio 2008\Projects\EFBeta2Windows\AdventureWorksModel\bin\Debug\Model.csdl|
 C:\Documents and Settings\julie\My Documents\Visual Studio 2008\Projects\EFBeta2Windows\AdventureWorksModel\bin\Debug\Model.ssdl|
 C:\Documents and Settings\julie\My Documents\Visual Studio 2008\Projects\EFBeta2Windows\AdventureWorksModel\bin\Debug\Model.msl;
provider=System.Data.SqlClient;
provider connection string=’Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True'” providerName=”System.Data.EntityClient” />
   

There’s a better way

In the ADO.NET Orcas forums, Craig Lee pointed out that the build task for EDMDeploy which creates these files is placed in the project file and can be edited. I went looking.

You can get at it right in the UI for editing the project.

Go to the Compile tab, then to Build Events.

You can see the EdmxDeploy code in the Post-build event command line.

By editing, you can see the whole thing and the target directory parameter.

I changed the target to point to an existing (easy access for demos!) folder on my drive.

Then after I built the model project again, the csdl, msl and ssdl files were all in c:\efmodels\aw.

Now I can easily modify the metadata paths of any projects that consume my model to simple paths such as “C:\efmodels\aw\model.csdl”.

If I am in a team environment, then my files need to be available on a shared location or from source control.

Just to see another aspect the before and after difference of serializable EntityKeys in the Beta2 of EntityFramework, I simply stored a single Customer entity into viewstate, posted back and then looked at the entity in debug to see that yes, the EntityKey is there.

I went back to my Beta1 VPC and when I try to add a single customer entity object to viewstate, I get all kinds of serialization errors. No point in analyzing them since it works now.

I’ll probably explore some asp.net databinding scenarios next.

In the forums, Craig Lee responds to the  problem reported in this thread:

We are planning on releasing an FAQ of known issues, but here is a preview to see if this helps you.

Issue
Visual Studio displays a Package Load Failure error message for Package ‘Microsoft.Data.Entity.Design.Package.MicrosoftDataEntityDesignPackage’ when you double-click on a .edmx file

Workaround

This issue is caused by earlier installations of the MCrit June CTP.

1.    If it is installed, uninstall the ADO.NET Entity Framework Tools CTP from “Add/Remove Programs” in control panel

2.    Use gacutil.exe to verify that the following DLLs are not in the GAC. 

a.    Microsoft.Data.Entity.Design.dll

b.    Microsoft.Data.Entity.Design.EntityDesigner.dll

c.    Microsoft.Data.Entity.Design.Package.dll

d.    Microsoft.Data.Tools.XmlDesignerBase.dll

3.    Use Regedit.exe to check for and remove the CodeBase value from the registry at:

a.    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Packages\{8889E051-B7F9-4781-BB33-2A36A9BDB3A5}

4.    Reinstall the the ADO.NET Entity Framework Tools CTP