Universal Get<>() accessor for any Linq-to-SQL Table

never write a Linq-to-SQL Get accessor again

I don't even want to know how many times I have written something like this:
var p = db.Products.FirstOrDefault(x => x.Id == someId);

Then 4 lines down, do it again almost exactly the same, over and over, at least once for each table. Well... no more! Now that I am able to get the Primary Key of any Linq-to-SQL talbe, it is trival to be able to write a universal get statement so I can simply do this when I want to grab an object by it's Primary Key:
Product p = db.Get<Product>(someId);

What if I want to get an item that has a Guid as a primary key? Same thing, it doesn't matter:
Guid gId = 
  new Guid("4fcc0b82-b137-4e4b-935e-872ed662ba53");
Gizmo g = db.Get<Gizmo>(gId);

If you you give the wrong Type of Key, it will tell you in a nice ArgumentException:
Gizmo g = db.Get<Gizmo>(5);

Error:

Primary Key of Table and primaryKey argument are not of the same Type; Primary Key of Table is of Type: System.Guid, primaryKey argument supplied is of Type: System.Int32



Here is the code without any error handling:
public static T Get<T>(this DataContext dataContext,
  object primaryKey) 
    where T : class, INotifyPropertyChanged
{
  return dataContext.GetTable(typeof(T))
    .Cast<T>()
    .Where(GetPrimaryKey<T>()
    .Name + ".Equals(@0)", primaryKey)
    .FirstOrDefault();
}

The full code is available in my Utilities class on CodePlex. This requires System.Linq.Dynamic.

Get the Primary Key PropertyInfo of any Linq-to-SQL Table

Easily find any table's Primary Key property

In my search for a universal generic Get() accessor for Linq-to-SQL DataContexts, I figured I would have to dynamically find the primary key of a table. Using Reflection and the Attributes applied to L2S tables, it is not difficult at all:
//get the primary key PropertyInfo table 'Product'
PropertyInfo info = GetPrimaryKey<Product>();

It's just that easy, it will throw a NotSupportedException if there is no primary key, or the primary key allows NULL. It uses the Linq-to-SQL ColumnAttribute properties to determine what the primary key is. If there is more than one primary key, it will just use the first one it comes across.

The complete source code can be browsed at my new Utilities Library along with full documentation on CodePlex, just figured it would be easy to keep all of my utilities in one place. Otherwise, here is the meat of the code:
public static PropertyInfo GetPrimaryKey<T>()
{
  PropertyInfo[] infos = typeof(T).GetProperties();
  PropertyInfo PKProperty = null;
  foreach (PropertyInfo info in infos)
  {
    var column = info.GetCustomAttributes(false)
     .Where(x => x.GetType() == typeof(ColumnAttribute))
     .FirstOrDefault(x => 
      ((ColumnAttribute)x).IsPrimaryKey && 
      ((ColumnAttribute)x).DbType.Contains("NOT NULL"));
  if (column != null)
  {
    PKProperty = info;
    break;
  }
  if (PKProperty == null) 
  {
    throw new NotSupportedException(
      typeof(T).ToString() + " has no Primary Key");
  }
  return PKProperty;
}

And yes, I did come up with a universal generic Get() accessor for Linq-to-SQL DataContexts, that's next post... or the code is already posted in my Utilities Library.

Forganizer - unobtrusive network file organizer - new open source project

An unobtrusive intranet ASP.NET MVC application for logically tagging, organizing and searching network files in one centralized location with a fast and simple interface

Me and some friends have a network set up where we share movies and music. This is a Windows domain network and we all simply share our folders as 'shares' across the network. The problem is that there are so many different files and they are all spread out, there was no way to browse them all at once in any sort of convenient way. That is where the idea for forganizer came up, it is simply a tagging and search interface for multiple shared drives (or just your local ones if you want). Since I hadn't really made any MVC projects yet, I decided to do this one in ASP.NET MVC, a great new technology. Also, this was my first attempt at many new programming techniques I hadn't really used in the past such as Unit Testing, Inversion of Control (Dependency Injection) and Mocking, all very useful!

I want to say right up front, this is for a Windows network, and will work best with... err... IE7+, because it works best with your file explorer - some features like folder opening and downloads may not work with other browsers.

You can always check out my source code if you want to see how something was done, here I am just going to explain what the program does from a user point of view.

clean install

Once forganizer is installed (just run Content/setup.sql and publish the rest to a website, making sure you have MVC installed and all that good stuff) you will see this:

Now you need to add some files to the system, here is the directory we are going to add for the demo. Notice that there are some files in the top folder, and also some folders that go down (quite a few nested folders actually).

adding some files

To add new files in, simply click on the 'manage' tab on the upper right. The default page for manage is 'add files' so you simply enter the network path in the 'from folder' field and click 'run it'.
Notice that it only uploaded 8 files, those are the files in the root. If you check the 'recursive' checkbox, if will go ahead and run through all of the files regardless of depth:
The system will never add a file more than once, so feel free to run the same folders over and over, in fact, that is how you enter new files that have been put on the shares - I think later I will implement and auto-updater. Now if we were to will in more of the textboxes the system would be more selective. Say I wanted to exclude all of the .txt files I have, I would just fill '.txt' in the 'exclude extensions' blank. Similarly, if I am only interested in video files, I would just put '.avi .mpg .mpeg' in the 'include only these extensions' field - it's really pretty self-explanatory.

now you are up and running

Click on the 'search' tab or on 'forganizer' and you will see that you now have a tidy list of files. Also, you will notice that on the right side, all of the file extensions that were in your folders are now represented in the extensions cloud, sized by the relation of file count - in my example, there are many more .cs files than anything else:
Each file has download, open folder and delete links next to it. They all do what you think they would, except delete does not actually delete a file, just the reference in forganizer. If you delete a reference, then later restore it, it will still hold all the tags you previously assigned to it.

tag some stuff

As of right now, this doesn't do us much good, they are searchable by file extension (just click on the file extension on the right), but you could do that in Explorer. We want to add some tags, so click on 'manage' again, and then on 'mass tag adding' on the right side menu.
In this example, I am adding the tag 'Testing' to all the files in the directory \\network_drive\share\folder\forganizer\Forganizer.Tests. Now go back to the search, and you can see that many of the files have been tagged:
The 'mass tag delete' and 'mass tag edit' work just the same as above (actually runs the same exact code). Notice that I also manually added the 'wallpaper' tags to a couple .jpg files - that was done simply by typing 'wallpaper' in the small textbox by each file and clicking '+tags' or pressing enter. You will also see that now there is a cloud tag on the right showing the tags that are in the present search.

create categories

Now you can make some categories to make searching easier. Categories are just groups of file extensions, I am going to make a 'programming' category that includes .js and .cs files:

the manual tagging interface

Now going back to the main search, you will see that a 'programming' category is now in the sidebar. In the following picture, you can see that I have clicked on the 'WebUI' tag and the '.js' file extension. Now all I see is the .js files that are tagged with 'WebUI', it's just that simple! Also take note of the intuitive urls achieved with MVC.
I also typed MS in some of the .js file blanks as I am going to tag those as MS javascript files by pressing enter:
Those MS tags are now inserted.

Now if you want to delete a tag, simply hover over it and a [delete] link will show up:
Click it and the tag is removed:
Pretty basic stuff and simple to figure out and use. This same add/delete interface is used for categories as well.

file cleanup

Now what if someone went and deleted some files, since forganizer does not actually constantly monitor the files, it will be out of date. That is why I built a cleanup feature - say I delete these files:

I can just go to manage -> file cleanup and click the big button:
And you are all cleaned up. One great feature on this is if any file is deleted, either manually or by the cleanup tool, its tags will be preserved; if that file is ever re-added, or restored, it will still have all the meta-data that it always had.

some more stuff

I went ahead and added the 'WebUI' tag to all the files in the \\network_drive\share\folder\forganizer\forganizer.WebUI folder, and 'DomainModel' to all files in the \\network_drive\share\folder\forganizer\forganizer.Tests folder, and finally 'forganizer' to all the files in the \\\network_drive\share\folder\forganizer\forganizer folder, so I can easily discern all of those files. I also added a 'visual_studio' category that include .csproj, .sln and .suo extensions, as well as a documents and images category.

So now I want to find all the Visual Studio related files in forganizer; all I need to do is click on 'visual_studio' in the categories, and 'forganizer' in the tags section and I get the files I am interested in:
Think of it now as "showing all the .csproj, .sln and .suo files with the forganizer tag" Notice that in the upper right, 'and' has a box around it, you can also choose 'or' and it will be a broader search. Say I clicked the 'or' link and then clicked the 'wallpaper' tag and 'images' as well, now forganizer would be showing "showing all the .csproj, .sln, .suo, .png, .jpg and .jpeg files with the forganizer tag OR the wallpaper tag"
You will also see that each cloud (tags/extensions/categories) will be broken into 2 parts, the active (green) links and the inactive (grey) links. The active links are files that are showing in the search right now, the inactive ones are things you can add to the search that will broaden it; if you have 'and' picked in the tag section, you will not see and inactive tags, as that wouldn't make sense.

remember, this is all unobtrusive

This never actually does anything with the files, deleting only deleted the reference, the files will still be there! This is just a layer on top to help sort/search.

I hope this is interesting to someone out there, if not, it was great to help learn MVC and some other great technologies.

Slick-Ticket v2.0 Released

Many improvements across the board, mostly behind the scenes

I am proud to say that I have released version 2 of my open-source trouble-ticketing/help desk system Slick-Ticket. I wasn't initially planning on making a new release, but the program has become suprisingly popular with over 3,000 downloads and an actual (small) community of users which is great!

You can download Slick-Ticket v2.0 if you have never used it before. If you have already been using a previous version, you will need to replace your existing code with the patching program, run it, then download version 2. It does a few database modifications to cleanup the lack of seperation of markup/db which was done very poorly in a rush of the first version. As with any upgrade, I highly recommend you backup prior to running this upgrade!

improvements

  • Broke the dbi.cs class up into more discreet classes
  • Renamed many methods properly
  • Removed (non-user-added) markup from any of the database fields
  • Moved some markup from the code-behinid to the .aspx pages
  • Globalized and ready for translation
  • Added ability to possibly delete attachments/comments/tickets in the future
  • Introduced some more dependency injection
  • Code is now much more robust!

Cloning an Entity in Linq-to-Entities

Making a clone of a record and popping it back into the database

There are a lot of reason you may want to do something like this, for me, users wanted to be able to make a copy of a huge record so they would then be able to go in and change a few things rather than make a whole new record which was very time consuming. At first, I though of pulling the item, manually copying each property, and inserting... but this is programming, there must be a better way. So then I thought about Reflection and how I might be able to work with that, but that became a big mess that I never got working prior to being discouraged. Next I hit Google, and an awesome blog had a great post to get me on the right track: http://damieng.com/blog/2009/04/12/linq-to-sql-tips-and-tricks-2.

That solution used Serialization to clone the entity, so simple, and so effective. The example they used there was Linq-to-SQL, but it will work for Linq-to-Entities just the same. I modified the code slightly to be used as an extension to basically any type... as this can really clone almost anything you throw at it (I think):
public static T Clone<T>(this T source)
{
  var dcs = new System.Runtime.Serialization
    .DataContractSerializer(typeof(T));
  using (var ms = new System.IO.MemoryStream())
  {
    dcs.WriteObject(ms, source);
    ms.Seek(0, System.IO.SeekOrigin.Begin);
    return (T)dcs.ReadObject(ms);
  }
}

That simple little bit of code will now clone *anything* - perfect! Now is just how to push it back into the db, which can be a little confusing. Initially, I tried this, which was intuitive to me:
MyEntities db = new MyEntities();
note n = db.note.First(nt => nt.note_id == some_int); 
note new_n = n.Clone();
db.AddTonote(new_n);
db.SaveChanges();

But was met with this error:
An object with the same key already exists in the ObjectStateManager. The ObjectStateManager cannot track multiple objects with the same key.

But, after actually thinking about it, that makes sense, since new_n is the same *exact* object as n, I can't really insert it into the DB, it is basically confusing the two. So to get around that, you have to detach the original Entity:
using (MyEntities db = new MyEntities())
{
  note n = db.note.First(nt => nt.note_id == some_int);
  note new_n = n.Clone();
  db.Detach(n);
  db.AddTonote(new_n);
  db.SaveChanges();
}

Closer, but still did not work, I now got the following error:
The object cannot be added to the ObjectStateManager because it already has an EntityKey. Use ObjectContext.Attach to attach an object that has an existing key.

Which also makes sense as you can't insert an Entity that already has an EntityKey. So one final change and we have a working clone:
using (MyEntities db = new MyEntities())
{
  note n = db.note.First(nt => nt.note_id == some_int);
  note new_n = n.Clone();
  db.Detach(n);
  new_n.EntityKey = null;
  // make any other changes you want on your clone here
  db.AddTonote(new_n);
  db.SaveChanges();
}

And no we have successfully cloned a record (Entity) and put a copy into the database. This would work very similarly for Linq-to-SQL, but I have not implemented it quite yet.

Universal IQueryable Search Usable by Linq-to-SQL and Linq-to-Entities

ever since Linq came out, I have been improving my simple 'google-like' searches; I think I found the holy grail

UPDATE 05/12/2009 - I have done a ton of updates to the class using Reflection, and a new version which is much more usable and simple will be out in the next day or two.

The 'quick search' is a great tool. Google has shown us that searching with one single, universal search field is the way that people prefer to search. Anyone who has worked with Linq for any amount of time knows this is possible, but it requires a lot of hard-coding and a long jumble of 'where' statements. This class will allow you to run a universal 'google-like' search on any IQueryable.

Now what I am going to show you here was a breakthrough to me, and it has made my life a whole lot easier. It may not be the best way to do this, but it is the best/fastest way I could figure out (hint: I am always looking for improvements), and it is totally portable for *any* multi-column IQueryable collection, plus it does multiple relations as well!

dynamic linq

First of all, this uses the System.Linq.Dynamic dll that was released a while back - it writes a dynamic query each search run, so this was necessary in my approach. If you do not have it, you can get it here: http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx.

data for the example

For this example, I am going to be working with Linq-to-SQL, but this works just as well with Linq-to-Entities and have used it in both types of projects. Here is the dummy data I am using for the demo:
CREATE TABLE dbo.category (
category_id  INT NOT NULL PRIMARY KEY IDENTITY,
category_name VARCHAR(MAX)
);

INSERT INTO dbo.category VALUES ('ninja');
INSERT INTO dbo.category VALUES ('pirate');
INSERT INTO dbo.category VALUES ('zombie');
INSERT INTO dbo.category VALUES ('weakling');

CREATE TABLE dbo._user (
_user_id INT NOT NULL PRIMARY KEY IDENTITY,
first_name VARCHAR(MAX),
last_name VARCHAR(MAX),
email_address VARCHAR(MAX),
category_id INT REFERENCES category(category_id)
);

INSERT INTO dbo._user VALUES 
  ('stan', 'naspinski', 'stan@mailinator.com', 2);
INSERT INTO dbo._user VALUES 
  ('sara', 'ortbals', 'hotchick@yahoo.com', 1);
INSERT INTO dbo._user VALUES
  ('rj', 'russell', 'punk@ilovedudes.com', 4);
INSERT INTO dbo._user VALUES 
  ('brian', 'pond', 'captweaksauce@ilovedudes.com', 4);
INSERT INTO dbo._user VALUES 
  ('idaho', 'edokpayi', 'idaho@yahoo.com', 1);
INSERT INTO dbo._user VALUES 
  ('phil', 'robles', 'crazyphil@hotmail.com', 3);
INSERT INTO dbo._user VALUES 
  ('jose', 'meza', 
    'iwishiwasasstrongasstan@gmail.com', 2);

Just a table of users with a referenced table of categories so I can demonstrate the multi-level search. Here is what my dbml looks like:
dbml

quick example

Now, say I want to do a universal search in all the fields for the word 'stan', it is as simple as this:
string[] columns_to_search = {"first_name", "last_name", "email_address", "category.category_name"};
string[] search_terms = {"stan"};
var search_results = new dbDataContext()._users.Search(columns_to_search, search_terms); 

All I did was specify which columns to include in the search, and what terms to search for; doesn't get much simpler than that. This is the simplest approach/overload, so you can get much more detailed if you want, and I will get into that later, but here you can see how powerful and simple this is. Here are the results when bound to a GridView:

search for 'stan'
search for 'stan'

This simply runs through the columns you specify and runs a Contains() against each field; if the keyword is found in one of those fields, it is considered to be a valid record. As you can see, the email address of of record 7 has the word 'stan' in it as well as the name and email address of record 1. Now if I narrow the search by adding another search term: 'pirate' you will see it narrows down the search to just one:

search for {'stan', 'pirate'}
search for 'stan', 'pirate'

Now you can not see 'pirate' in this GridView as it is just an auto-generated GridView, but 'pirate' is the value of the 'category.category_name' record 2 which is referenced in the last column (chopped off a bit); so you can see how easy it is to use relations with this tool.

what is happening

It is pretty simple what is happening. For each object that is passed in the 'keywords' the IQueryable is whittled down so only the ones that 'pass' remain:

searching with 'stan' and 'pirate' (psuedocode)
IQueryable results;
results = dbDataContext._users.Where(any_of_the_specified_fields.Contains(first term));
results = results.Where(any_of_the_specified_fields.Contains(second_term));
results = results.Where(any_of_the_specified_fields.Contains(third_term));
...and so on...

Note that this only does a single db call to get the first IQueryable (in the case your IQueryable is from a database), once that one is in memory, all searches are done against it, so it is pretty fast.

the search textbox

Now that is all very simple, but rarely are your searches going to be hard-coded, so it would be simple to code in a button handler to search through a specified textbox with something like this:
protected void btnSearch_Click(object sender, EventArgs e)
{
  char[] search_delimiters = { ',', ' ', ';', '+' };
  string[] columns_to_search =  {"first_name", "last_name", "email_address", "category.category_name"};
  
  gvSearch.DataSource = new dbDataContext()._users
    .Search( columns_to_search, txtSearch.Text.Split(search_delimiters));
  gvSearch.DataBind();
}

Pretty basic stuff there, you could also get your columns to search from a user interface like a bunch of CheckBoxes or a ListBox control.

getting more specific

If you look into the code, you can see it gets more and more specific the more you drill down into it. With the above overload, it assumes you are using all string comparable fields and all of your inputs are strings. The class's abilities do not stop there, and can get more and more specific. Say you had a massive table with the following fields: int named 'ID', DateTime named 'birthday', text named 'name' and bool named 'male'. If you simply tried the above overload with that, it would error as the types int, DateTime and bool do not have Contains() methods. But, if you specify name/type, you can use this to great advantage:
Dictionary ColumnNamesAndTypes =  new Dictionary<string, Type>();
ColumnNamesAndTypes.Add("ID", typeof(int));
ColumnNamesAndTypes.Add("first_played_videogames", typeof(DateTime));
ColumnNamesAndTypes.Add("name", typeof(string));
ColumnNamesAndTypes.Add("male", typeof(bool));

object[] search_for = {18, 25, DateTime.Parse("12/08/1981"), true, "a","b","c" };

var search_results = largeTable.Search(ColumnNamesAndTypes.ToArray(), search_for);

Now, since the Search now knows the types, it will only run valid types against the fields; for example, the 'true' object will only be run against the 'male' field, as it is the only 'bool' field, the values '18' and '25' are ints, so they will only be compared to the 'age' field of type 'int'. So, what this search would do is return all the people in the IQueryable 'largeTable' that are ages 18 and 25, who 'first_played_videogames' on 12/08/1981, that have an a,b or c in their names who are male. Now I know you can easily write these queries in Linq yourself, but if somehting changes, you are hardcoding a search change that is easy to screw up, this does it all for you.

parsing varying strings

Now there is a little more difficulty with attempting to pass in data through the magical 'google-like' single search box, but it really isn't that hard, check this out:
protected object[] parseSearchString(string search_string)
{
    int obInt;
    DateTime obDt;
    bool obBool;

    char[] search_delimiters = { ',', ' ', ';', '+' };
    List<object> obs = new List<object>();

    foreach (string s in search_string.Split(search_delimiters))
    {
        if (Int32.TryParse(s, out obInt)) obs.Add(obInt);
        else if (DateTime.TryParse(s, out obDt)) obs.Add(obDt);
        else if (bool.TryParse(s, out obBool)) obs.Add(obBool);
        else obs.Add(s); // else it's a string
    }
    return obs.ToArray();
}

Now I obviously didn't go through all the types there, but you get the idea. This takes in a string from a textbox and tries to parse it out to the more specific types before it defaults to a string. Now you might run into conflicts when you want to use integers as strings and such, but if you are this far in this post and still understanding things, I am sure you can figure out a way to code around that. Going back to the original example, now if I wanted to search the '_users' table for either 'category_id' (which is an integer) or the name of that category, I would just use the following:
protected void btnSearch_Click(object sender, EventArgs e)
{
  Dictionary<string, Type> columns_to_search = new Dictionary<string, Type>();
  columns_to_search.Add("category_id", typeof(Int32));
  columns_to_search.Add("category.category_name", typeof(string));
  gvSearch.DataSource = new dbDataContext()._users
    .Search( columns_to_search, parseSearchString(txtSearch.Text));
  gvSearch.DataBind();
}

And as you can see that uses the above 'parseSearchString' so if I enter in '1', it is searched as in Int32:
search for '1'

And if I type in 'ninja' is is searched with a 'string' (ninja is the string value of category_id 1):
search for 'ninja'

that's it

I am hoping that at least a few of you can decipher my ramblings... hopefully you can see how this can be useful to you, and with any luck, it will save you some time and headaches. If people are interested, I can go into the code and how it works, but for now I will just leave you with the source (I even wrote comments... something I am horrible at). I am hopeing someone knows how to get the fields/types automatically from a generic IQueryable so this can be made even better - if you know how to do that, please share!!!

Linq-to-SQL vs Linq-to-Entities : Revisited

After 2 months of nothing but L2E, I feel I can give a decent comparison

Most likely you have read more than just a few of these comparisons and are tired of the debate. I too disliked L2E from the get-go but decided that my opinion was not totally grounded and that I should really understand L2E before I crap on it, so, I decided to do an entire project at work with it and get down to the nuts and bolts. Now, 2 months, a lot of frustration and some happy customers later, I can give a fully educated opinion on L2E: it is not as bad as I originally thought, in fact, it is pretty sweet in some areas, but still not as easy to use as L2S.

If you have read any of my multiple posts on Entities this will mostly be a re-hash of what I have already said. If not, this is a good summary of my findings and hopefully will help you decide.

dynamic queries

Winner: TIE
Thanks to the geniuses that wrote System.Linq.Dynamic.dll the syntax is *mostly* interchangable between the two languages (which I didn't realize when writing this). If I was forced to pick a winner, it would be L2S as there are more choices for syntax than for L2E, but that can also be viewed as a fault.

linq-to-sql -and- linq-to-entities
var results = db.branch
  .Where("branch_id == @0 || display == @1", 
    new object[] {5, "Hello"});

With L2S, you can do this as well:
var results = db.branch
  .Where("branch_id == 5 || display == \"Hello\"");

working with databound controls

Winner: Linq-to-SQL
This one is a no-brainer. After being lulled into the fact that L2S just works with so many things, I had to work backwards on problems I found with L2E. One annoyance I noticed was that if I was making an update in a GridView, it would error out unless I explicitly had the primary key in the GridView - and if I wanted it hidden, it was necessary to use css; it is documented here.

Another thing I ran across is the fact that you need to use all the required 'Include' statements in order to get a Repeater/GridView to show child values of relationships with Entities where is L2S, you could simply call the relation without any extra work.

multiple databases

Winner: Linq-to-SQL
The choice is quite simple here, as Linq-to-Entities has no native support for multiple databases.

database relation management

Winner: Linq-to-SQL
First of all, simple things such as getting foreign key values with linq-to-entities is much more difficult than in L2S; examples (written as short as I could):

linq-to-sql
int s_id = dataContext.records
  .First(r => r.record_id == 1).state_id;

linq-to-entities
int s_id = Convert.ToInt32(
  Entities.records.First(r => r.record_id == 1)
    .stateReference.EntityKey
    .EntityKeyValues.First().Value
  );

As for putting those relation values into the DB, the clear winner is L2S as well, just take a look at what it takes to set this object 'r' to have a reference to the 'city' which has a key of the integer value of 5:

linq-to-sql
r.city_id = 5;

linq-to-entities
r.cityReference.EntityKey = new EntityKey(
  "YourEntitiesNamespace.cities", "city_id", 5);

a few more...

A while back I went over some other reasons L2E annoyed me such as DataSource annoyances, relations (again) and the pain of updating your model.

verdict

Linq-to-Entities is fully capable and can do *almost* everything Linq-to-SQL can do, it just takes a bit more code some times; the almost is for multiple database utilization. L2E is a great ORM, but many of us were spoiled with L2S, so it is now up to Microsoft to live up to the expectations that they put out for us with this framework that they are deprecating. I hope and assume it will become a superior ORM, but that is in Microsoft's hands now; for the time being, it is not.

Shout it kick it on DotNetKicks.com

You might be making a lot of unneccessary trips to your DB with Linq-to-SQL

Linq-to-SQL has great use of relations, but lazy loading can cause a huge amount of trips to your DB - implementing eager loading is one answer

After my rant on L2E, Dan from Microsoft got back to me in the comments and pointed out a lot of what I wanted to know. One thing that he brought up was the use of eager loading which I hadn't looked in to; turns out it can be VERY important!

Let's say you are using a GridView and for each row, you are using a relation to the 'states' table to pull the name of a 'state' object:
<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

Now this will work just fine, but each time you add a row, there is going to be another call to the DB to get the state name. Now if you only have a few rows, no big deal, but what if you are calling 100 records each with a a call to the state name and a country name? It is no longer one trip to get the items, it grows huge! Here is some ugly psuedo-code for understanding:
ONE_SQL_QUERY -> 100_ROWS_RETURNED (1 trip)
FOREACH(ROW row in 100_ROWS_RETURNED)
{
  QUERY_FOR_STATE_NAME (1 trip)
  QUERY_FOR_COUNTRY_NAME (1 trip)
}

That one query has now jumped to 201 database calls (1+100(1+1)) ... that is no small difference! Now, that first query, no matter how big and ugly it is is dwarfed by these petty little calls for one small string. Just imagine what happens if you are calling multiple and nested relations, that one call can easily jump to well over 1000 in no time.

So what to do?

While searching on Linq-to-Entities (yes, I still want to make the switch some time) I came across this: http://blogs.msdn.com/adonet/archive/2008/10/07/migrating-from-linq-to-sql-to-entity-framework-eager-loading.aspx which shows how you deal with this in both L2S and L2E (also answered my last question on my rant against L2E. This is actually a situation where (I think) L2E shines over L2S, but that's a whole other debate. Here is what you would need to do.
dbDataContext db = new dbDataContext();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<item>(i => i.state);
dlo.LoadWith<item>(i => i.country);
db.LoadOptions = dlo;

What this is doing is telling your DataContext to load those other elements with your every 'item' you load (don't forget to include using System.Data.Linq;). This will obviously make for a larger query to begin with, but it will cut out those multiple return trips that are the real problem!

On an somewhat related note... I am going to force myself to use L2E on my next project.

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):

DataSources

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:

Linq-To-Entities

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

Linq-to-SQL

<asp:LinqDataSource ID="ldsState" runat="server"
  ContextTypeName="dbDataContext"
  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.

Relations

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:

Linq-To-Entities

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

Linq-to-SQL

item itm = new item() { item_name = "party", state_id = 5 };
db.items.InsertOnSubmit(itm);
db.SubmitChanges();

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.

Linq-To-Entities

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

Linq-to-SQL

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

Linq-To-Entities

Who knows?!

Linq-to-SQL

<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 DotNetKicks.com

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;
db.AddToproducts(p);
db.SaveChanges();

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));
db.SaveChanges();

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";
db.SaveChanges();

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";
db.SaveChanges();

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();
db.DeleteObject(del);
db.SaveChanges();

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

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);
db.AddToproducts(p2);
db.AddToproducts(p3);

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;

db.AddToreviews(r1_1);
db.AddToreviews(r2_1);
db.AddToreviews(r3_1);
db.AddToreviews(r3_2);

db.SaveChanges();

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;'>");
  p.reviews.Load(); // this loads all the reviews that relate to the product p
  foreach (reviews r in p.reviews)
    Response.Write("<b>" + r.title + " - " + r.reviewer + "</b><br />" + r.review + "<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 = db.reviews.First(_r => _r.review_id == 3);
r.productsReference.Load();
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...