We all know by now that table foreign keys are not directly accessible in Entity Framework and that searching on the subject on Google or Bing brings confusion and despair, at least it did to me. MSDN was not better, so I decided to go the trial and error route instead and I was not sorry.

I have found that, as with the .NET Framework, Linq often has a 1 liner solution to a problem, so my attitude was simple: find it. And so I did.

The trick is resolved with a simple and non-obscured object and it looks like this:

Entity.Reference.EntityKey = new EntityKey("Entities.Entity", "ReferencedColumn", "Value"));

Oddly enough, and contrary to what I have read, this command works for inserting, editing and deleting the foreign key. There are some caveats though, if you want to be able to delete the key, you’ll have to have the field / property nullabe in both the database and object.

Here is how I typically handle an insert, edit, delete situation:

private void Save(int ID)
        {
            try
            {

                int _id = Convert.ToInt32(ID);

                using (ObjectEntities db = new ObjectEntities())
                {
                    //Add a new object
                    if (_id == 0)
                    {
                        var _object = new object();

                        _object.Name = this.txtName.Text.Trim();

                        if (String.IsNullOrEmpty(this.SomeDropDown.SelectedItem.Value) == false)
                        {
                            _object.EntityReference.EntityKey = new 
                                    EntityKey("ObjectEntities.Entity", "ID",
                                        Convert.ToInt32(this.SomeDropDown.SelectedItem.Value));
                        }
                        else
                        {
                            _object.EntityReference.EntityKey = null; ;
                        }

                        //Some additional variable assignement

                        db.AddToEntity(_object);
                        db.SaveChanges();

                        // Rebuild the Grid

                        // return to the list
                    }
                    else
                    //Edit the object
                    {
                        var _object = ((from Item in db.Entity
                                        where Item.ID == _id
                                        select Item)
                                      .FirstOrDefault());

                        _object.Name = this.txtName.Text.Trim();

                        if (String.IsNullOrEmpty(this.SomeDropDown.SelectedItem.Value) == false)
                        {
                            _object.EntityReference.EntityKey = new    
                                    EntityKey("ObjectEntities.Entity", "ID",
                                        Convert.ToInt32(this.SomeDropDown.SelectedItem.Value));
                        }
                        else
                        {
                            _object.EntityReference.EntityKey = null; ;
                        }

                        db.SaveChanges();

                        // Reload the current item with the changes 
                    }
                }
                // Do some logging 
            }
            catch (Exception ex)
            {
                //Output some message about the error.
            }
        }

And that is all the is to it.

Related Links:

http://msdn.microsoft.com/en-us/library/dd283138.aspx 

http://msdn.microsoft.com/en-us/library/system.data.objects.dataclasses.ientitywithkey.entitykey.aspx

I was searching the net for a simple example of a create,edit, delete with Linq-to-Entities, I eventually found but it was too elaborate, so I thought I would give my 2 cents worth and give a simple example. When I embarked on my learning mission of Linq-to-Entities it was to simplify my life, sometimes I wonder if this was the case, I still love Linq though.

Creating a new record is as easy as this:

using (DataEntities db = new DataEntities())
{
//Create a new NewsItem
var newsItem = new NewsItem();
//Assign values	
newsItem.Headline = this.txtHeadline.Text.Trim();
newsItem.PublishedDate = Convert.ToDateTime(this.txtPublishDate.Text.Trim());
newsItem.Active = this.cbActive.Checked;
newsItem.Synopsis = this.txtSynopsys.Text.Trim();
newsItem.Content = this.edContent.Text;
newsItem.Author = this.txtAuthor.Text.Trim();
//Add the new item to the set 
db.AddToNewsSet(_news);
//Commit the changes
db.SaveChanges();
}

Editing a record is slightly more complicated, first you have to have the record if it is not persisted as is the case in ASP.NET.

using (DataEntities db = new DataEntities())
{
//Get the NewsItem for editing
var newsItem = ((from NewsItem in db.NewsSet
where NewsItem.ID == newsID
select NewsItem)
.FirstOrDefault());
//Assign values	
newsItem.Headline = this.txtHeadline.Text.Trim();
newsItem.PublishedDate = Convert.ToDateTime(this.txtPublishDate.Text.Trim());
newsItem.Active = this.cbActive.Checked;
newsItem.Synopsis = this.txtSynopsys.Text.Trim();
newsItem.Content = this.edContent.Text;
newsItem.Author = this.txtAuthor.Text.Trim();
//Commit the changes
db.SaveChanges();
}

Lastly, deleting a record

using (DataEntities db = new DataEntities())
{
//Get the NewsItem for editing
var newsItem = ((from NewsItem in db.NewsSet
where NewsItem.ID == newsID
select NewsItem)
.FirstOrDefault());
//Set the record for deletion from the database
db.DeleteObject(newsItem); 
//Commit the changes
db.SaveChanges();
}

As can be seen there are no complications and Linq just does it.

In my next Linq article I will be talking about relational data editing.

I am currently writing a small website for one of my companies clients and decided that I should use LINQ-to-Entities and the ADO.NET Entities Framework to make my life easier and to learn a bit more about LINQ. You may recall a statement I did a few months ago on Twitter “ADO.NET Entity Framework sucks! Linq Rocks!” well I stand by that but it has some serious shortcomings, I would call it a bug but then it would be a documented bug. You cannot use any Entity.Field.ToString in a Linq block!

So the following code will fail:

using (Entities db = new Entities())
{
    var news = (
                   (
                       from N in db.NewsSet
                       where N.PublishedDate <= DateTime.Now
                       orderby N.PublishedDate descending
                       select new
                       {
                           Date = N.PublishedDate.ToString("dddd, dd MMMM yyyy"),
                           Headline = N.Headline,
                           Article = N.Content,
                           Author = N.Author,
                       })
                   ).First();

    this.litHeadline.Text = news.Headline.Trim();
    this.lblDate.Text = news.Date.Trim();
    this.litArticle.Text = news.Article.Trim();

}

The error is: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

The line that will fail is Date = N.PublishedDate.ToString("dddd, dd MMMM yyyy") because I use ToString, which in my opinion is ridiculous. Why can I not format or manipulate the variable as I wish? Microsoft, please fix this or remove the Intellisence that allows me to do that. See be llow.

The actual code that works is here bellow:

using (Entities db = new Entities())
{
    var news = (
                   (
                       from N in db.NewsSet
                       where N.PublishedDate <= DateTime.Now
                       orderby N.PublishedDate descending
                       select new
                       {
                           Date = N.PublishedDate,
                           Headline = N.Headline,
                           Article = N.Content,
                           Author = N.Author,
                       })
                   ).First();

    this.litHeadline.Text = news.Headline.Trim();
    this.lblDate.Text = news.Date.ToString("dddd, dd MMMM yyyy");
    this.litArticle.Text = news.Article.Trim();

}

For more information Muhammad Mosa has a good article called Linq to Entities, what is not supported? I highly recommend that you read it along with Microsoft’s MSDN Supported and Unsupported Methods (Linq to Entities) 

Next Dynamic Linq…

This is a follow up article from Legacy data with Entity Framework.

One can only love Linq, however this is not true about the ADO.NET Entity Framework and therefore Linq to Entities. With the announcement of the abolishment of Linq to SQL I would of thought that EF would be a really good replacement. I now can tell you that Microsoft still has a lot to do to get it right.

EF feels clumsy, it does not react as expected. For example, I create an entity from table “tbl_Customers” and name the entity “Customers” I expect all references to that entity to be “Customers” but this is not the case. I now create an Entity called “Regions” from table “tbl_Regions” and do a reference from RegionID in Region to RegionID in Customers. I then expect to be able to call Customers in Regions as follows Regions.Cutomers simple. However, if the property “” is not set as well as the name I have to the call as follows Regions.tbl_Customers. Why should I have to name anything more then the entity name? If that is not clumsy I really don’t know what is.

Furthermore, creating inherited entities is next to impossible, the fact is that you have to use NULLS in your table to differentiate between entities, using legacy tables this was not an option and even when you manage there is a lot of other issues. Let me elaborate

I should call this post “Learning the practical side of ADO.NET Entity Framework” since it’s the first time I use it in a real world application. I generally do not like to use new technologies for the sake of it. However, this seemed to be the right time and opportunity to learn ADO.NET Entity Framework in a real world situation. A quick fix to a complex problem, I needed to “plug-in” a new web application into a legacy database, the catch was that I would have to change the database latter and this new database would have a new schema. ADO.NET Entity Framework would allow me to create my new schema with the old one and map it to the new database latter. Great to avoid to do the work twice…

The first thing I found out with the ADO.NET Entity Framework is that it is fiddly at best. I thought it was going to be the easiest solution and to be entirely honest had I coded my DAL myself as I used to, I’d be finished!

Right, I am not entirely truthful, I am using a database with garbage in it. But still, if I have to use NULL’s to differentiate between object types it becomes a problem, I do not believe in NULL values in a database.

The situation is as follows, I have a legacy table that contains product categories and actual products, the table’s data is really dirty. So I want to create 2 Entity Objects one called “Categories” and another called “Products” and let the ADO.NET Entity Framework do the differentiation using a column with a true or false, easy! well it does not work! I kept getting a mapping error in Visual Studio 2008. After some research on the net I found out, to my dismay, that differentiators can only use NULL’s. So, the product category record must have a NULL value in some arbitrary field to be recognised as a category object by the ADO.NET Entity Framework, how ridiculous!

So, I cleaned the data and set a field to NULL, set the clean renamed CategoriesnProducts object to Abstract = True. Next, I created 2 objects Categories and Products both inheriting from CategoriesnProducts then mapped my legacy table to those new entities. At which time I found that all the fields in the legacy table where flagged Not NULL, so I took those flags off, even though it meant changing the database, I knew that it would not affect any of the other applications. Refreshed the schema. Sort out the new entities mappings and voila… I had a successful, error free build.

On the bright side, a many-to-many relationship is a breathe in the ADO.NET Entity. It maps it all out and abstracts it completely as seen in the relationship between CategoriesnProducts and LegalDocuments.

Next Linq-to-Entities.