Andreas Lehmann : SQL Server 2008

Dynamic where with Entity Framework

alehmann — Wed, 18 Mar 2009 17:18:54 GMT

In my last project I have to implement a dynamic where filter for some date stored in the Enitity Framework (EF) model. The next picture shows the DB schema of the data.

This is a common scenario to store multiple properties of a single item in a very dynamic way. The TItemProp table has a foreign key relation to the TItem table. The PropKey column in the TItemProp table holds the key of the Property (e.g. “Name”) and the PropValue column stores the value of that property (e.g. “Stephe”).

Now I have to filter some items from the TItem table with some specific properties using the EF in a simple one-to-one mapping (for more I will wait for v2 ;-).

Using LINQ to Entity a static way could be:

        1: using (EFTestEntities context = new EFTestEntities())

       2: {

       3:     var items = from item in context.TItem.Include("TItemProp")

       4:                 where item.TItemProp.Count(ip => ip.PropKey == "PropA" && ip.PropValue == "abc") > 0 &&

       5:                       item.TItemProp.Count(ip => ip.PropKey == "PropB" && ip.PropValue == "cde") > 0

       6:                 select item;

       7:  

       8:     return items.ToList();

       9: }

That is what I want to do. But I need it a bit more dynamic…

       1: using (EFTestEntities context = new EFTestEntities())

       2: {

       3:     var items = from item in context.TItem.Include("TItemProp")

       4:                 select item;

       5:  

       6:     items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == "PropA" && ip.PropValue == "abc")) > 0);

       7:     items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == "PropB" && ip.PropValue == "cde")) > 0);

       8:  

       9:     return items.ToList();

      10: }

That gives me what I want to do, but still the list of parameters is not dynamic. Therefore I will create a Dictionary which holds the Key Value collection and use a foreach loop to build the filter.

       1: Dictionary<string, string> props = new Dictionary<string, string>();

       2: props.Add("PropA", "abc");

       3: props.Add("PropB", "cde");

       4:  

       5: using (EFTestEntities context = new EFTestEntities())

       6: {

       7:     var items = from item in context.TItem.Include("TItemProp")

       8:                 select item;

       9:  

      10:     foreach (var prop in props)

      11:     {

      12:         items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == prop.Key && ip.PropValue == prop.Value)) > 0);

      13:     }

      14:  

      15:     return items.ToList();

      16: }

I thought I was done right now, but when I run my project I recognized, that my result has changed for my last approach with the foreach loop. What went wrong here, it took me some time with the SQL-Profile to figure out happen here. The generated SQL statements of all three codes where identical but in the last one the parameters went wrong. Instead of passing P1 = “PropA”, P2 = “abc”, P3 = “Propb” and P4 = “cde” the last version passes P1 = “PropB”, P2 = “cde”, P3 = “Propb” and P4 = “cde”. That where two times the last parameter. If I change the order of the parameters in my Dictionary the other parameter was passed twice. To solve this proplem I have to introduce two more variables; key and value. Assigning the values first to the variables and using them in the LINQ statement results in the correct behavior. Here is the code that worked for me:

       1: Dictionary<string, string> props = new Dictionary<string, string>();

       2:  

       3: props.Add("PropA", "abc");

       4: props.Add("PropB", "cde");

       5:  

       6: using (EFTestEntities context = new EFTestEntities())

       7: {

       8:     var items = from item in context.TItem.Include("TItemProp")

       9:                 select item;

      10:  

      11:     foreach (var prop in props)

      12:     {

      13:         string key = prop.Key.ToString();

      14:         string value = prop.Value.ToString();

      15:  

      16:         items = items.Where(i => (i.TItemProp.Count(ip => ip.PropKey == key && ip.PropValue == value)) > 0);

      17:     }

      18:  

      19:     return items.ToList();

      20: }

There seems to be some strange behavior LINQ to EF is handling passed parameters. I think this is related to the deferred execution of LINQ statements and that the reference to the specific key value pair get lost.

So be careful by passing values to a LINQ statement!

Saving not permitted in SQL Server Management Studio 2008

alehmann — Thu, 04 Sep 2008 19:01:00 GMT

"Saving changes is not permitted. The changes you have made require the following tables to be dropped ..."

I found that this is one of the most annoying default settings I ever saw in a development tool.
In this dialog you can save the text to a text file (not very helpful here) or cancel the save action of the item you are working on.

To prevent SQL Server Management Studio 2008 to show that message while you are developing a database schema go to the Options.
Tools -> Options -> Designers -> Table and Database Designer and uncheck the "Prevent saving changes that require table re-creation".

Trouble on installing SQL Server 2008 RTM on developer machines

alehmann — Mon, 11 Aug 2008 08:34:00 GMT

As you can read in many blogs around the world there is an issue installing the RTM of SQL Server 2008 on developer machines with the RTM or the SP1 Beta version of Visual Studio 2008. Here is the corresponding text from the ReleaseNotes of SQL Server:

KB Article 956139 refers to Visual Studio 2008 SP1, which is not available until the week of August 11, 2008. You should not install the Visual Studio 2008 SP1 Beta to meet the Visual Studio 2008 SP1 requirement for installing SQL Server 2008. Visual Studio 2008 SP1 Beta does not provide the necessary functionality for installing SQL Server 2008. The following describes options for installing SQL Server 2008 before Visual Studio 2008 SP1 is available.

If Visual Studio 2008 is not currently installed on your computer, you can install SQL Server 2008 and no further action for installing Visual Studio is required.
If Visual Studio 2008 is currently installed on your computer, you cannot install SQL Server 2008 successfully unless you perform one of the following actions:
- Wait until Visual Studio 2008 SP1 is available and then install it before installing SQL Server 2008.
- Uninstall Visual Studio 2008 and then install SQL Server 2008. After Visual Studio 2008 SP1 is available, you can install Visual Studio 2008 and then install Visual Studio 2008 SP1.
- Do not install these components of SQL Server 2008 that require Visual Studio 2008: Management Tools (both Basic or Complete), Integration Services, and Business Intelligence Development Studio features.

You can find the whole document here: http://download.microsoft.com/download/4/9/e/49eeb41a-a769-4520-80d6-671b8ae2bd06/SQLServer2008ReleaseNotes.htm

RTM version of SQL Server 2008 is now available for MSDN subscribers

alehmann — Thu, 07 Aug 2008 06:43:00 GMT

The RTM (Release to Manufacturing) version of SQL Server 2008 is now available for MSDN subscribers. The following versions are ready for download:

· SQL Server 2008 Developer (x86, x64, ia64) - DVD (English)

· SQL Server 2008 Enterprise (x86, x64, ia64) - DVD (English)

· SQL Server 2008 Standard (x86, x64) - DVD (English)

· SQL Server 2008 Web (x86, x64) - DVD (English)

· SQL Server 2008 Workgroup (x86, x64) - DVD (English)

If you are an MSDN subscriber you can download it here:
http://msdn.microsoft.com/en-us/subscriptions/downloads/default.aspx?PV=42:334

By the way, Microsoft although released the SQL Server Compact 3.5 SP1 as described in this blog post: http://msdnrss.thecoderblogs.com/2008/08/06/sql-server-compact-35-sp1-released/