Multiple databases in Linq-to-Entities?

Yeah... good luck with that

Yet another HUGE downfall of L2E was found today in my forced learning of this (quickly becoming dreaded) technology.  I have several databases that may need to connect to one central db that was written ages ago with a classic ASP program.  It is basically voodoo magic to us, so we don't touch it, just pull data out of there as we need it.  In the past, if I wanted to do such things with L2S, I would simply drag the table onto my DBML from my Server Explorer and use it like any other Linq-to-SQL object.  Not the case with L2E, apparently it is completely 'out of scope'.  In fact it looks as if you basically have to do some sort of magical workaround to get this to somewhat work (and forget about updating the edmx file automatically).


So, regretably, I have been reduced to using oldschool ADO with DataReaders, SqlConnections and SqlCommands.  Why do you make me hate you so L2E, why?  I hope MS is taking notice of these many shortcomings!

Getting Foreign Key Values with Linq-to-Entities

A little more complicated that just calling the property...

Once again, L2E makes life a bit tougher. Let us say that we have a database record with a foreign key 'state_id' that links to a 'states' table. In this said record, we want to get the integer value of that 'state_id' column (which is the unique identifier/primary key of the states table). BUT, we don't want to pull the state information, we just want the indentifier. Once again, in L2S, this was trivial:

var a_record = 
  dataContext.records.First(r => r.record_id == 1);
int s_id = a_record.state_id;

Doesn't get much simpler than that, and this makes perfect sense. But L2E does not allow this... why I do not know, but you can't simply call on the property 'state_id' because it is not there.

// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state_id; // doesn't exist

This will not work either:

// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state.state_id; // not populated

Instead, the following *will* work, but it pulls the 'state' and that is an extra pull we don't need:

// this works
var a_record = 
  .First(r => r.record_id == 1);
int s_id = record.state.state_id; // populated this time

Granted this example is small, but what if that was a table with a giant-sized record?

// the proper way
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = Convert.ToInt32(record.stateReference

Wow, that is a lot of code to get a simple integer. This is so aggravating to me because that integer is clearly stored in the DB, but there is no clean way to get it, sp insteada you have to go through the references EntityKey.

Instead, I often write a static extension so I can simply call some_record.stated_id() to get the integer:

public static int isff_tracker_id(this state s)
  return Convert.ToInt32(record.stateReference.EntityKey

Now, I am not L2E master, but if someone can point out a better way to do this, I would be greatful! This forced learning of L2E reminds me daily why I will stick with L2S next time.

Getting a Repeater/GridView to show child values of relationships with Entities

Once again, it is a bit more involved than L2S, but relatively simple

I have made L2S my go-to for a while now, and I will keep referring back to it as a baseline of how ORMs *should* work. When I am using a repeater, and I want to show a related object value, I can simply do this with L2S (same syntax for L2E as well):
<%# Eval("state.state_name") %>

Where 'state' is a related object. Similarly, if I try the same thing in L2E, it will not error out, it will just show up blank (I would much prefer an error). Like I said, the syntax is the exact same for that part, it is just different when you are getting your data. You now need to explicitly include what child objects you want to load with your data. So, for the above example, I could something like this:

var results = from p in db.records select p;
Repeater1.DataSource = results;

But, to get anything other than blanks to show up with L2E, I need to change it a bit:

var results = from p in db.records.Include("state") 
  select p;
Repeater1.DataSource = results;

Now it knows that 'state' has to be loaded with the original records. You can chain mulitple Include()s along to include more and more stuff as well:

var results = from p in 
  select p;

Populating Entities Foreign Key Relationships with EntityKey (and no extra trip to the db)

Relationships in Entities as not as obvious as they are in Linq-to-SQL

Despite my reluctance to step into the darkside of L2E, I decided that my current project I am forcing myself to use it. It is bound to be (as it has already proven) frustrating and painful, but I think it's a good idea to learn.

First problem I have run into is inserting records with relationships. In L2S, it is very simple and the syntax could not be easier. If I have a record, and it has a relation to a table of cities, I could simply make a new 'record' and set the 'city_id' to the integer primary key of the city table:

record r = new record() { city_id = 5 };

But, with L2E, it is not that simple. In the past, I had shown how to relate a record using a full trip to the server to get the entire entity; but that is not efficient and/or the right way to do it. Now why we can't do something more similar to above will hopefully be covered in the next release, but here is as close as I can seem to get the syntax:

record r = new record();
r.cityReference.EntityKey = new EntityKey(
  "YourEntitiesNamespace.cities", "city_id", 5);

Now, this is not nearly as clean as the L2S, but it does not require a trip to the db and isn't too bad. You will notice that the record has a property 'cityReference' - this is automatically made by the edmx because there is a foreign key reference in the table from record->cities. Then the new EntityKey just takes in 3 parameters (it is overloaded with other ways to input as well):
  • string qualifiedEntitySetName which will be your entityNameSpace.TableName
  • string keyName your primary key field
  • object keyValue the value in the primary key field

Why I decided to stick with Linq-to-SQL over Linq-to-Entities

I tried to make the switch... but Linq-to-Entities is just so much more work!

Recently I tried, I really tried to like L2E, but I just can't do it! It adds layer upon layers of extra crap I have to do over L2S while offering little to no improvements over the soon to be deprecated ORM. I am officially sticking with L2S until they get L2E better developed. Now I am hardly the first person to write about this, but I feel my concrete examples are somewhat helpful when trying to decide.

Now I have read a bunch of reason of why not to switch, but I stubbornly went along and tried it myself. I like to dumb down my reasons to simple examples of why I have come to loathe L2E (these are all using the same exact DB):


I'll just show you on this one, it may not seem like a lot, but try it for yourself and see how confusing the syntax can get:


<asp:EntityDataSource ID="edsState" runat="server"
  EntitySetName="state" Select="it.[state_id], it.[state_name]">


<asp:LinqDataSource ID="ldsState" runat="server"
  Select="new (state_id, state_name)" TableName="states" />

Now I don't know about you, but the L2S is just easier to understand. Why the hell do I have to use it.[column_name]? That doesn't make any sense. Not to mention the GUI may as well not be there if you are trying to use automatic Delete, Update or Insert.


This one is HUGE! Let's say you have a Foreign Key relation of state_id that relates to a table states and Primary Key that is also named state_id; here is how you do that in both frameworks:


item itm = new item() { item_name = "party" };
itm.state = db.state.First(s => s.state_id = 5));


item itm = new item() { item_name = "party", state_id = 5 };

Now this is a super abbreviated case, but you can see the huge amount of overhead this can create not to mention programming time. I made these as succinct as I could not to try to advantage one over the other and the L2E is just burdensome. Even if I were to make a simple Get() method for state it would still make another call to the DB when I already know the relation integer!!! What an unnecessary step. And this extra work increases like crazy on large object with multiple relations.

Updating your Model

Now I can't relaly show this as an example, so I will just try to explain it. One big pain (I thought) in L2S was the fact that you had to pull your tables back in to your dbml every time you made a change to the DB (I know there are tools for this, but I am talking stock). L2E had this nifty feature that allowed you to 'refresh from the database' that I thought would actually work. The problem is, that if you change anything with any complexity whatsoever, like a Foreign Key, or even something as simple as changing a data type from VARCHAR to INT it seems to completely break your edmx. And this isn't simple to fix like a dbml, not even close. You can't drag a table into a edmx, and you can't seem to fix it through the refresh so what do you do?
  • Delete your edmx completely
  • Go into your web.config and delete the ConnectionString (unless you want duplicates)
  • Completely remake your edmx
  • Redo any changes in the crappy naming conventions to ALL of your tables that you changed (more on this later)
Wow... all that because I changed a relation? No thank you. Dragging that table into the dbml doesn't seem so bad now.

Relations Part 2

This one isn't as big of a deal, but it is annoying.


item itm = db.item.First(i => i.item_id == 12);
string whatState = itm.stateReference.Value.state_name;


item itm = db.items.First(i => i.item_id == 12);
string whatState = itm.state.state_name;

Just simpler in L2S and you don't have to remember the Load() if you forget, and it's in a lot of code, it can be extremely aggravating wondering why your relation is empty. Not to mention that reference.value syntax in there... more unnecessary (in my eyes) crap.

Relations Part 3

And this was the straw that broke the camels back... I was so used to the simplicity and convenience of L2S that this drove me nuts and was the reason I decided to give up. Relations are used so well with L2S that to show relations in something like a Repeater or GridView is almost trivially easy


Who knows?!


<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

but since L2E requires that wonderful Load() method, it is now not so easy - to tell you the truth, I never figured it out how to make it work, probably have to call something on the RowDataBound event, but at this point I figured what is the point?

I am officially resigning from L2E (for now) and going back to L2S... maybe next go-round L2E won't be such a pain? Isn't new technology supposed to make our lives easier? Going from L2S to L2E is like 'upgrading' from a blender to a hand-mixer; it will get done, it will just take you longer and be more frustrating. Hopefully, considering that is the was MS is heading.

Shout it kick it on

Using orderby with an EntityDataSource

This can be confusing when you are selecting all (*)

In a LinqDataSource if you want to sort by a column called 'item_id' you simply put:

but if you use that syntax with a EntityDataSource you will get this error:
System.Data.EntitySqlException: 'group_name' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier

But in an EntityDataSource you will need to do:

Notice that it will always be it. That confused me for a while... the problem is that if you are selecting certain items and not using the auto-update, insert or delete features, the orderby dialogue in the GIU is very simple, but when you want to use these features, it mysteriously disappears.

Getting started with Linq-To-Entities tutorial

The transition from Linq-to-SQL to .Net's Entities framework is incredibly simple

In my humble opinion, Linq is easily the greatest thing .Net has come out with in the past few years, and along with it, Linq-to-SQL (L2S) was a godsend. Being quick to hop on the L2S bandwagon, I quickly became a huge fan of the framework, it is amazing easy and useful. That being as it is, I was quite disappointed when I heard that Linq-to-SQL was no longer going to be advanced at all. Now, it is surely not dead, it is still very usable and effective at what it does, but I like to stay with frameworks that will be actively advanced and fbug-fixed. So I decided to make the jump to Linq-to-Entities (L2E), and it was suprisingly simple.

This guide should be a good starting point for anyone whether or not they are familiar with L2S, and a quick 'jumping guide' to L2S developers. Here's how to get started:

Make your ADO.NET Entity Data Model (.edmx file)

This is comparable to the .dbml file that you made with L2S.
  • Right click on your App_Code folder and select New Item
  • Select ADO.NET Entity Data Model and name it (I left the default Model.edmx for the example)
  • Choose Generate from database and click Next
  • Choose your database from the dropdown and choose the name to save the ConnectionString as and click Next
  • Choose all the things you want included, I just chose tables, but you may want to include views and SPs if you have them
  • *Be sure to remember the Model Namespace you choose for your .edmx (I will use DatabaseModel for the example) - click Finish

Now it will take a moment to run through and produce your .edmx; when it is done you will see a nice little representation of your tables reminiscent of the .dbml display in L2S:

Access your .edmx

Now we simply access the .edmx much like we did with L2S. You must remember to add Using DatabaseModel (or whatever your namespace was) to the code where you are using it. Now we will make a DatabaseModel object and use it to add a 'product' to the database. First, you need to make your Entity Access Object:
DatabaseEntities db = new DatabaseEntities();

Then you can use it to enter an object:
products p = new products();
p.product_name = "Word Processing Software";
p.price = 99;
p.stock = 100;

Once again, if you are familiar with L2S, this is almost the same exact thing! If you are new, this is very straight-forward and should be easy to understand:
  • Make an object of a type inside your database
  • Fill the object up with data
  • Put it into the database
  • Save the changes

Now L2E one-ups L2S and makes data entry even easier, this will accomplish the same as above:
db.AddToproducts(products.Createproducts(0, "Accounting Software", 300, 125));

Notice that the 'product_id' is entered as '0', that is because it is auto-incrementing, so it doesn't matter what I put there, it will be ignored. Now I don't know about you, but that little bit there will save me hundreds of lines of code! I am starting to like L2E already!

Display your data

Now that we have put a few objects in to the database, we can go and check it out. Later we will dig in with code, but first we will use a LinqDatasource/GridView:
  • Drag a LinqDataSource (LDS) on to the page and click Configure Data Source from the little square on the upper right of the LDS
  • Choose your Object Model and click Next
  • Choose your table and click Finished
  • Drag a GridView (GV) on to your page
  • In the GV option box, choose your datasource from the dropdown

now just view your page:

Modify Data

Now that we have seen how to put in data, we will modify some; once again, L2E makes it trivially simple:
products edit = (from p in db.products where p.product_id == 2 select p).First();
edit.product_name = "Account Software v2";

The Linq statement is EXACTLY like it would be in L2S, and just like in L2S, you can shorten up this with some Lambda integration; this will accomplish the same thing:
db.products.First(p => p.product_id == 2).product_name = "Accounting Software v2.1";

Deleting Items
Now that we have seen Insert and Update, the next logical step is Delete, and it is just as easy. Let us delete the 'Word Processing Software' frorm the table:
products del = (from p in db.products where p.product_id == 1 select p).First();

And the same exact thing shorthand with Lambdas:
db.DeleteObject(db.products.First(p => p.product_id == 1));

Once again, I have to say I like the approach that L2E takes of that of L2S, as it is not necessary to specify which table to delete from, as an object can only be deleted frorm the table that it is in.

Using your database relations

As in L2S, L2E takes great advantage of well designed databases and relations. If you noticed up above, the two tables in my database have a 1 to many relation frorm product to reviews. Each element in the 'reviews' table is required to relate to an element in the 'products' table (column 'product_id'). Let's fill the DB with a few more products and some reviews; a lot is going to go on here:
products p1 = db.products.First(p => p.product_id == 2);
products p2 = products.Createproducts(0, "Strategy Game", 49, 99);
products p3 = products.Createproducts(0, "Sports Game", 39, 99);

reviews r1_1 = reviews.Createreviews(0, "Much Improved", "this is a much better version", "Bill Brasky");
r1_1.productsReference.Value = p1;
reviews r2_1 = reviews.Createreviews(0, "Terrible", "worthless", "Dirk Digler");
r2_1.productsReference.Value = p2;
reviews r3_1 = reviews.Createreviews(0, "Great Game", "very tough AI", "Wonderboy");
r3_1.productsReference.Value = p3;
reviews r3_2 = reviews.Createreviews(0, "Very Fun", "the Bears rule", "Mike Ditka");
r3_2.productsReference.Value = p3;



Now to explain what just happened:
  • The first line gets an object of type products where product_id == 2 (this is 'Accounting Software v2.1')
  • The next two lines create new products
  • The next two submit those into the database

Now we have a total of 3 objects in the 'products' table and none in the 'review' table, that is where the next 8 lines come in. If you break up those 8 lines into pairs of two, you can see they are all the same thing really:
  • Make a new object of type reviews
  • Assign its foreign key reference to a products object

Since the database requires a relation between these two tables, you must be sure to set the reference. The last lines just submit everything and commit them to the database.

Now that that is in there, you can use the real power of relations in L2E. Once again, it is almost the same as L2S:
foreach (products p in (from _p in db.products select _p))
  Response.Write("<h3>" + p.product_name + " - $" + p.price + "</h3>");
  Response.Write("<b>Reviews:</b><div style='border:1px solid navy;padding:5px;'>");; // this loads all the reviews that relate to the product p
  foreach (reviews r in
    Response.Write("<b>" + r.title + " - " + r.reviewer + "</b><br />" + + "<br />");
  Response.Write("</div><br />");

And this is what you get:
Yes, I know I used the ugly 'Response.Write', and the output is hideous... but this is just a demo people! As you can see, it is incredibly easy to iterate through all of the reviews of a products, this big difference being that you need to call the .Load() method or they will not be populated. Now this is both a blessing and a curse; this is very efficient as it only loads the related objects if need be, but... it is soemthing that is easy to forget and may leave you scratching your head. I think I like it.

Now relations also work the other way as well. Say you have a reviews object, and you want to know the price of the products it is related to. No need to actually look up the products object, you just call the relation:
reviews r = => _r.review_id == 3);
Response.Write("Price: $" + r.productsReference.Value.price.ToString());

Once again, notice that you have to Load() the reference, or you will get nothing.

Now that should get you started. Like I said, if you are familiar with L2S, this transition should be no problem, and if you are new to this whole Linq arena, this should be simple to pick up. This new ADO is getting more and more impressive the more MS works on it. I can't even imagine goging back to the old methods...