I'm a massive fan of LINQPad - it's much more lightweight than SQL Management Studio if you want to muck around with your data, and allows me to work against SQL with C#, rather than having to remember how to use cursors and the like when I've got some otherwise tedious data re-mapping to do.
That said, I'd never really pointed it at an Entity Framework EDM before, but I'd been wondering about the best way to tidy up the code for the tag clouds in the albums section of this site, so thought that I should see what I could do with the entity model as it stood, without resorting to stored procs.
Firing up the "Add Connection" wizard, I selected "Entity Framework" from the "Use typed data context from your own assembly", selected the website library, selected the (only) EDM from the library, and confirmed the provider.
LINQPad then created the data context correctly, so I pulled up a query to start working against it, only to get the following error message:
An assembly with the same identity 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has already been imported. Try removing one of the duplicate references.
This was slightly confusing - obviously both my project and LINQPad are referencing the System.Data.Entity namespace, and if I remove the reference from my project, I won't be able to build it. How do I remove it from LINQPad then?
It turns out that I don't have to do either. The clue (for me) lay in the notes section of the "Using LINQPad with Entity Framework" page:
If your assembly references other assemblies, LINQPad will pick up these, too, providing they live in the same folder.
Note the bit in bold from the original page - it would appear that my reference to the System.Data.Entity library was set to "Copy Local". Setting that to false and restarting LINQPad removed the error message, allowing me to clean up the tag code somewhat.
Filed under: LINQ
As I said in my last post, I've rebuilt the site using ASP.NET MVC - other view engines are available, might be better, or at least better suited to your ideology, but this was the impetus I needed to actually start looking beyond the realm of Webforms.
The other main shift was from LINQ to SQL to using the Entity Framework (LINQ to Entities) as the data layer - once again, other ORMs are available, and don't have the baggage associated with being produced by Microsoft, but they also don't have the visual tooling - and while this site is primarily a place for me to play with cool new shiny toys, I do have a life, and more importantly a family, and didn't have all the time to learn how to configure them correctly or use them.
What I'm going to talk about today is my experiences moving from LINQ to SQL, some of the reasons behind why I moved, the benefits I saw, and the pain points I encountered - and to be honest, from what I've seen at the last two Tech.Eds, and read online, it looks like a lot of those pain points have been removed - although I'm sure there will a future post where I go over the "issues" I've encountered when upgrading 
One of the key things that I disliked about LINQ to SQL was the fact that there was an absolute mapping between the tables in your database, and the classes in your model:
This is an image from my LINQ to SQL DBML file, and it looks exactly the same as the ERD that I'd see in SQL Management Studio's diagram of the database - finding the text for all the tags for a given image required code such as:
For each (var tag in photo.siteContent_TagsPhoto.siteContent_Tags) {
tag.Text;
}
Which (regardless of the naming convention) was painful, and unpleasant, so what I ended up doing was creating a set of helper objects, with sensible names, that had the right collection of properties on them, including a list of tags, that I would then use as:
public static List<Photo> GetPhotos(int AlbumID) {
var zhpCtx = new ZhaphCoreContentDataContext([...]);
bool? filter = !(HttpContext.Current.User.IsInRole("Editors") ||
HttpContext.Current.User.IsInRole("SiteAdmin"));
var photos = from p in zhpCtx.siteContent_Photos
join a in zhpCtx.siteContent_Albums on p.AlbumID equals a.AlbumID
where p.AlbumID == AlbumID && (a.IsPublic == filter || a.IsPublic)
orderby p.PhotoID descending
select new {p.PhotoID, p.AlbumID, p.Caption};
var results = new List<Photo>(photos.Count());
foreach (var photo in photos) {
results.Add(new Photo(photo.PhotoID,
photo.AlbumID,
photo.Caption,
GetTags(photo.PhotoID, zhpCtx)));
}
return results;
}
private static List<string> GetTags(int photoID,
ZhaphCoreContentDataContext zhpCtx) {
return (from tp in zhpCtx.siteContent_TagsPhotos
join t in zhpCtx.siteContent_Tags on tp.TagID equals t.TagID
where tp.PhotoID == photoID
select t.TagText).ToList();
}
There are some nice things in there - I can easily select just the elements from siteContent_Photos that I want - so I'm not pulling back the actual image data from the photo objects when I don't need it, however I've had to jump through all those hoops to get the text of the tags into the results list.
So what did I end up with Entity Framework?
Notice that I've lost the object representing the mapping table between Photos and Tags - great! I can now go directly from a Photo to its Tags, and from a Tag to the Photos.
BUT, this wasn't without its issues.
There were two main issues I had with Entity Framework version 1:
-
I was unable to lazy load properties in Entity Framework as I had done in LINQ to SQL
-
It was a lot harder to filter on complex CLR entities in Entity Framework than it was in LINQ to SQL.
Taking those points in greater detail then.
Lazy Loading Properties
As I showed above, I was able to easily select just those properties from an object that I wanted to load, projecting them out into a new class:
var photos = from p in zhpCtx.siteContent_Photos
[...]
select new {p.PhotoID, p.AlbumID, p.Caption};
So photos contains a set of implicitly typed objects, that have three properties: PhotoID, AlbumID and Caption - this is effectively the same as using the following SQL statement:
SELECT [t0].[PhotoID], [t0].[AlbumID], [t0].[Caption]
FROM [siteContent_Photos] AS [t0]
All that is retrieved from the database then is those three columns.
In Entity Framework I would say (method name has changed, but the result was the same, an object with a number of photos in it):
public Album GetAlbum(int albumId, bool loadPhotos) {
ObjectQuery<Album> albums = loadPhotos ?
m_DoodleContent.Albums.Include("Photos").Include("Photos.Tags") :
m_DoodleContent.Albums;
return albums.FirstOrDefault(a => a.AlbumId == albumId);
}
Which ended up loading all the content for each photo in the album - the reason I had to do it this way was that Entity Framework doesn't expose the AlbumID property of the Photo object either - so there was no way to say "give me all the photos with an AlbumID equal to x" as I had done in LINQ to SQL.
This also shows one futher issue that I've not fully resolved - as the TagsPhoto table is hidden from my object model, I can't query directly into that to build up the Tag Cloud that I had previously - were I'd been able to perform a GROUP in the SQL and get the COUNT of the tag ids from the TagsPhotos table, and then find the TagText for each tag.
Filtering on complex CLR types
Now, I've read a few explainations of why this behaviour exists, but none of them really convinced me fully.
In LINQ to SQL I could write the following, basically saying "get a blog post where the path (after the date) is equal to "path", and the Date the post was published on is equal to the date part of "publishedDate"":
BlogPosts post = (from blogs in blogPosts
where blogs.PostPath == path &&
blogs.Published.Date == publishedDate.Date
select blogs).SingleOrDefault();
However, that wouldn't work with Entity Framework - changing "SingleOrDefault" to "FirstOrDefault" allowed it to compile, but the site would throw nasty exceptions at runtime, along the lines of "System.NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities".
I initially worked around this by searching for all posts with a given path, calling ToList() on the collection, and then searching that for date, but I didn't like that either, as it (obviously) loaded all the potentially matching blogs before it found just the one I wanted.
What finally worked for me was:
BlogPost post = (from blogs in blogPosts
where blogs.PostPath == path
&& blogs.Published.Year == publishedDate.Year
&& blogs.Published.Month == publishedDate.Month
&& blogs.Published.Day == publishedDate.Day
select blogs).FirstOrDefault();
As Entity Framework did know how to pull out the Year, Month and Day properties of a DateTime object.
Overall neither of these were particularly big issues, but they caused some of the delays in getting the latest version of this site live - especially as most of the work on it is done late at night
Filed under: LINQ
We had the following issue: We'd gone back to an old .dbml file, and added a new table on to the design surface. Visual Studio proceeded to check out the project file, and delete the .designer.cs file from the system - which we failed to notice initially, as it's a nested file. What we did notice however was that nothing would compile, especially not our DAL, as the partial class within the data context contained lots of red squiggly lines and complained that most of our data objects no longer existed.
Looks like I'm not the only one seeing this, and MS fixed it last month, but I'm not sure when the fix is coming out.
Thankfully, there is a work around, and it seemed to work quite well for us:
In your partial class, move any using statements inside the namespace:
namespace DataContexts {
using System.Collections.Generic;
using System.Linq;
partial class ConfigDataContext
{
[...]
}
}
Then right-click on the .dbml file in Solution explorer, and select "Run Custom Tool". This will then regenerate the designer files, and your code will now compile.
Filed under: LINQ
So, Visual Studio 2008 is now in RTM, which also means that LINQ has moved out of Beta. Yay.
However, I've found some breaking changes between the Beta 2 and RTM bits:
-
Table<t> no longer has a method
Remove() - this has been renamed to DeleteOnSubmit(). This apparently is to "better convey that the updates are deferred until the call to DataContext.SubmitChanges()" (MSDN Forums)
-
The XML declaration of the .DBML file has changed, in Beta 2 this was:
<?markup version="1.0" encoding="utf-16"?>
In the RTM this has changed to:
<?markup version="1.0" encoding="utf-8"?>
Curiously, this UTF-16 caused VS to complain about unicode support, and wouldn't open the design surface. Changing it to UTF-8 fixed the issue.
I'll post more as I find them.
Filed under: LINQ
Well, finally, we get some guidence on the DataSources for LINQ in Windows Forms applications.
This seems better than my hack of editing the generated partial class that I posted about in September (Playing with LINQ For SQL in a Windows Forms Application).
Filed under: LINQ
Don’t look now, but I think this might actually be a technical posting!
So, I’ve finally had an excuse to start playing around with LINQ – some data clean up tasks that I could have done by hand, but it’s always more fun to throw together a little app or two than spend your life in Enterprise Manager (or SQL Studio).
The key issues I came up against were with DataGrids, and picking and populating columns.
A fairly simple setup for editing the Taxonomy (remember, this is a developer application, so it works for me):
We have a ComboBox providing the Category, and then the DataGrid allows us to edit the Subjects. The “Subject ID” is an auto-generated GUID that is occasionally useful for the developer to know, so I wanted to display it, the data model looks like this:
As you can see there’s an additional column in the database that I’m not interested in for the DataGrid: “TAX_CATEGORY_ID” this is always powered by the Category selected in the combo box, so I didn’t want to have to set it every time.
Part 1: Designer Support for LINQ Database Models (DataContext)
When wiring up the DataGrid, I initially tried the following:
editSubjects.DataSource = from ts in db.TaxonomySubjects
where ts.TAX_CATEGORY_ID == (Guid)chooseCategory.SelectedValue
orderby ts.SORT_ORDER
select new { ts.TAX_SUBJECT_ID, ts.SORT_ORDER, ts.DISPLAY_NAME, ts.METADATA_NAME };
This almost worked – I had the columns I wanted, but I couldn’t edit the values at all – it turns out that if you use an anonymous type you lose the ability to edit it – the DataSource doesn’t know how to safely update the object, so disables editing.
On top of that, you have no way to change the column properties of the DataGrid in the designer.
I’d read about the ASP.Net LinqDataSource control, and so (naively) assumed there’d be a similar one for WinForms, but apparently not, and after hunting around and reading the readme of the samples that come with VS 2008 (namely the WinFormsDataBinding sample), I found the follow “solution”:
-
Add a BindingSource to your form
-
In the .Designer.cs class, add the following in the InitializeComponent() method:
this.taxonomySubjectsBinding.DataSource = typeof(MetadataManager.TaxonomySubject);
Replacing the binding name with your binding name, and the value in typeof() with the value from your “Linq To Sql” classes
-
You can now assign that BindingSource to your DataGrid and get access to the column builder:
As you can just about see, the DataGrid doesn’t display the TAX_CATEGORY_ID column although it is there, and I’ve been able to supply nice names for the columns.
My data source wire up now looks like this:
editSubjects.DataSource = from ts in db.TaxonomySubjects
where ts.TAX_CATEGORY_ID == (Guid)chooseCategory.SelectedValue
orderby ts.SORT_ORDER
select ts
Which is just taking all the TaxonomySubject objects that match the query, and the DataSource is then filtering their properties into the correct columns without me having to worry about anything else.
Part 2: Populating hidden columns/Auto Generated Ids in Linq DataContexts
Having successfully cleaned up my DataGrid columns, I now had two remaining problems to solve around adding a new record:
-
Generating the new GUID for the subject.
-
Adding the TAX_CATEGORY_ID value to the hidden column.
In the database, the TAX_SUBJECT_ID field is defined as:
[TAX_SUBJECT_ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TAX_SUBJECTS_TAX_SUBJ_ID]
DEFAULT (newid()),
So, it’s the primary key column, with a default value of a new GUID. This is where our problems begin.
LINQ understands that SQL can auto-generate the values for columns on a database, provided:
Primary keys that are designated IsDbGenerated=true should also have a DBType with the IDENTITY modifier.
Note that my column here is not marked as an Identity column – to use the IsDbGenerated property, anything that goes in that column must be an integer.
I noticed that my DataContext class contained the following methods in a region called “Extensibility Method Definitions”:
partial void InsertTaxonomyCategory(TaxonomyCategory instance);
partial void UpdateTaxonomyCategory(TaxonomyCategory instance);
partial void DeleteTaxonomyCategory(TaxonomyCategory instance);
partial void InsertTaxonomySubject(TaxonomySubject instance);
partial void UpdateTaxonomySubject(TaxonomySubject instance);
partial void DeleteTaxonomySubject(TaxonomySubject instance);
So, let’s implement some of them in a new class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MetadataManager {
public partial class MetadataDatabaseDataContext {
partial void InsertTaxonomySubject(TaxonomySubject instance) {
if (Guid.Empty == instance.TAX_SUBJECT_ID) {
instance.TAX_SUBJECT_ID = Guid.NewGuid();
}
this.ExecuteDynamicInsert(instance);
}
}
}
A really key thing to note here – if you create these methods, even as empty methods, you must call this.ExecuteDynamicInsert(instance); otherwise any changes you try to submit will be lost.
Basically, before the changes are inserted into the database, this method fires for each one, and I’m creating a new GUID if the column is empty.
When adding just one new row this seems to work ok, but as soon as I try and add more rows, or two at a time, I get exceptions like:
"Cannot add an entity with a key that is already in use."
Hardly ideal – the key’s not in use, but I guess there’s something else I’m missing – any thoughts? On top of that, there’s no way to insert the Category ID into the foreign key column in this method.
So the final solution I came up with to resolve this was to hook into the RowEntered event on the DataGrid:
private void editSubjects_RowEnter(object sender, DataGridViewCellEventArgs e) {
int rowIndex = e.RowIndex;
if (null == editSubjects["taxSubjectId", rowIndex].Value) {
editSubjects["taxSubjectId", rowIndex].Value = Guid.NewGuid();
}
if (null == editSubjects["taxCategoryId", rowIndex].Value) {
editSubjects["taxCategoryId", rowIndex].Value = (Guid)chooseCategory.SelectedValue;
}
}
This lets me generate a new GUID for the row, and adds the Category’s ID to the hidden column.
Which feels to me like an awful cludgy hack (but then so does a lot of the LINQ stuff as well – it’s all inline SQL really isn’t it? Isn’t that supposed to be bad?), but then I read the rest of ScottGu’s blog post – to get a custom query hooked up to a LinqDataSource you have to hook into the Selecting event and replace the result property with your new query…
Any thoughts, let me know.
Filed under: LINQ