My old Linq-to-Entities project came back to haunt me. I had to make some changes to the DB to allow a couple things, and with that, I changed some INT NOT NULL REFERENCES change to INT REFERENCES - the obvious difference being that they now allowed null values. No big deal really, I went into my .edmx file and did 'Update Model from Database' and everything seemed to be working fine, until I tried a certain operation that kicked out this doozy:

A relationship is being added or deleted from an AssociationSet 'FK__Gizmo__CategoryI__0425A276'. With cardinality constraints, a corresponding 'Gizmo' must also be added or deleted.

Now those arent the actual values, but you get the idea. This is telling me that my Foreign Key relationship is being violated, which confused me. I had changed that FK to be nullable, so this should not be happenening. I then tried the same operation in SQL Server Management Studio, just to be sure it was legal on the SQL side, and it worked fine. So I figured, like so many times before, the problem lies with Linq-to-Entities. I opened my .edmx and saw something like this:
As you can see, it clearly shows a one-to-many relationship from Category->Gizmo. This was no longer the case, but L2E failed to pick up on it.

The bottom line is that 'Update Model from Database' did not catch the Foreign Key change

Once I figured this out, it is a simple to fix. Simply right-click on the relationship (in the box above) and click 'Properties'; that will bring up the Properties dialogue. Once this is open, change the End of the referenced table from '1 (One)' to '0...1 (Zero or One)' and save your .edmx.



This should not be necessary as you would assume 'Update Model from Database' would catch things like this, but like so many other things in L2E, it just doesn't work like you want it to. I can't wait for .Net 4.0, supposedly most of the problems with L2E are getting fixed; we'll just have to wait and see.

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.

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.

My IQueryableSearch class has been infinitely useful to me and saved me a ton of time. I have also gotten some good feedback on how useful it is. For those of you that don't know what it does (probably most of you) it is simply a universal search for Linq IQueryable collections; a way to search a bunch of fields/properties on a bunch of objects with one simple interface, like google for your own objects.

With that said, I decided to put it up on CodePlex to better track source code and releases; as just posting them as zips on my blog is a pain when the code is being updated. I am also hoping maybe some of you might want to critique/fix/add to my code to make it better - any interest is appreciated. As always, I hope it helps.

To make it easier it is now available in a dll which you can simply put into your bin, add a:

using Naspinski.IQueryableSearch;

And you are ready to start using it.

On a somewhat related note, I am getting close to releasing another large open-source project I have been working on for quite some time that should prove to save huge amounts of time for ASP.net programmer - stay tuned!

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.