For some time I am consulting one of my customers with the creation of WPF application which uses a Sql Server Compact edition DB.
We quickly used Linq to SQL as our DAL and created our data model (using SqlMetal as so far even after SP1 no IDE support for SqlCE, drop me a comment if you don’t know how to use it).
As with everything in life, Linq to SQL has it’s weaknesses but to tell the truth it just works. I’ve been able to put up a full working POC consisting of Composite application guidance and everything in just a few hours work.
Last week I’ve returned back after a short vacation, PDC and some nice work at Redmond.
One of the new things I found out after getting back is that there is a possibly new requirement, a need to work against DB2.
Thinking about the future in this project brought me to several conclusions:
- We would like to have one DAL.
- We would like to be able to work against SQL Server, SQL server CE and DB2
- We would like to be able to model our objects from those sources.
As you might suspect, Entity framework should be a good investment to obtain those conclusions written above. It should now support my development against SQLCE and when the time is right (assuming a provider for DB2 will be shipped) we will be ready for it.
So I started migrating our App to EF.
Linq to SQL:
When you would like to retrieve elements and their associated elements as well in one query, you would have to register it using DataLoadOptions object.
it is very convenient one line of code and setting it inside the DataContext factory and you no longer need to get bothered with it again. It just work.
The EF equivalent is using .Include() method with your query. Instead of supplying lambda expression which you got intellisense, this time you need to supply it with a string; the name of the table. This is error prone as hell, do I need to mention table name changes or just honest misspelling?
More cumbersome than that you need to add it to every query you run.
Got an arch object with a lot of associated entities, tough luck, Include got only one parameter so you need to chain them.
Again, for every query!
Linq to SQL:
Lucky for us the data of the app is readonly. We don’t need our DAL to keep track of changes, because there will be none.
So naturally we are using[DataContext].ObjectTrackingEnabled = false.
Unfortunately the EF corresponding object (ObjectContext) doesn’t include such an easy option.
You need to manually check every table for it’s MergeOption and assign it to MergeOption.NoTracking.
I repeat for every table, if you have +100 tables, be prepared for a nice copy & paste finger exercise, who said developers only sit on our buts and do no physical exercises.
One of the key advantages of EF is the ability to model the entities differently than the db. You want to add an association using the designer. I was attacked with mind boggling errors such as :
Error 2 Error 3021: Problem in Mapping Fragment starting at line 1406: Each of the following columns in table XYZ_Product_CHILDTABLE is mapped to multiple conceptual side properties:
XYZ_Product_CHILDTABLE.AAAAID is mapped to <XYZ_Product_XYZ_Product_CHILDTABLE .XYZ_Product_CHILDTABLE .AAAAID , XYZ_Product_GRAPHICProductXYZ_Product_CHILDTABLE .XYZ_Product_GRAPHICProduct.AAAAID _ID>
XYZ_Product_CHILDTABLE.SuperProductID is mapped to <XYZ_Product_GRAPHICProductXYZ_Product_CHILDTABLE.XYZ_Product_CHILDTABLE.SuperProductID, XYZ_Product_GRAPHAlProductXYZ_Product_CHILDTABLE.XYZ_Product_GRAPHICProduct.SuperProductID>
Rough edges ah?
The last issue of many I am going to publish here is that a innocent looking query condition such as : Name == “Margol” raises this issue:
The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.
Apparently there is a bug and somebody along the way thinks that my honest nvarchar Name column of mine is NText, so the == is not valid against it.
Reading this MSDN forum thread, will let us know that a solution has been found and should be released in the near future, joy.
Well for me it was enough, I’m not to wait for it, I am back to Linq to SQL, and I don’t care of what the guys at ADO.net team are thinking.
See you next version.