Tutorial: DataBinding from Entity Framework to a WPF DataGrid

Let us say that you are an experienced .NET WinForms programmer, you have written a hundred forms that have a stupid DataGrid, you have bound countless data models to these grids, and frankly, although you know how to do it very well, you are sick of it all, and hope you’ll never see a single DataGrid ever again.
And then your boss comes in and tells you he had a chat with a friend, and that friend told him that WPF is the word in our days.
And now you’ve got to convert the whole application to WPF! What a bummer!
Every first encounter with WPF for a WinForms programmer is traumatic, as it’s a totally different world, and anything you knew till now is invalid.
What’s even worse – there’s no good documentation.
Of course, there are books, but nobody reads books in our time, we just want to go to Google, copy paste some code, and be done with it, don’t we?
Even MSDN is not very clear on how to do pretty basic things.
What usually scares newbie WPF programmers is the fact that you have no ability to access the Grid’s rows directly, you have no option to see when rows are added or deleted, and generally speaking – you can’t control anything.
However, once you are done reading this article, you’ll not only understand how easy it is to bind data to a WPF DataGrid, but you’ll also be amazed how elegant it is.
Anyway, let’s jump into business.
You’ve got a Data Model you’ve got through Entity Framework, and all you have to do is bind it to that stupid WPF DataGrid, make it editable, make it deletable, and make it possible to add new Entities.
For simplicity let’s define our model like this(I’ve removed all the Entity Framework metadata to make the thing more readable):

class Car
{
    int ID { get; set; }
    string ModelName{ get; set; }
    int MakeYear { get; set; }
    int CarMakerID { get; set; }
    CarMaker CarMaker { get; set; }
}
 
class CarMaker
{
    int ID { get; set; }
    string Name { get; set; }
}

First, let’s define our DataGrid to contain only the flat info about our cars (ID,Model,MakeYear), and make the CarMaker name readonly.
Don’t worry we are going to let our user pick the CarMaker soon enough.

<DataGrid AutoGenerateColumns="False" Name="carsGrid">
    <DataGrid.Columns>
        <DataGridTextColumn Binding="{Binding Path=ID}" Header="ID" IsReadOnly="True" />
        <DataGridTextColumn Binding="{Binding Path=CarMaker.Name}" Header="Car Maker" IsReadOnly="True" />
        <DataGridTextColumn Binding="{Binding Path=ModelName}" Header="ModelName" />
        <DataGridTextColumn Binding="{Binding Path=MakeYear}" Header="Make Year" />
    </DataGrid.Columns>
</DataGrid>

A couple of things you should notice:
• The “Binding” Attribute: this is where you bind columns to properties.
It could be either a flat property such as ModelName and MakeYear, or a complex mapping such as CarMaker.Name.
• We did not mark in any way that MakeYear is a numerical value, but WPF will take care of all that by itself.
If the user tries to submit non-numeric characters there, the cell will be marked red, and data will not be saved.
Now for the DataBinding itself, we do not bind the data directly, but rather use an ObeservableCollection, you’ll see why in a second.

private void BindData()
{
    var dataSource = new ObservableCollection<ImapHost>(context.Car);
    dataSource.CollectionChanged += CollectionChanged;
    carsGrid.ItemsSource = dataSource;
    carsGrid.DataContext = dataSource;
}

And here’s why:

private void CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
{
    if (e.Action == NotifyCollectionChangedAction.Add)
        foreach (Car car in e.NewItems)
            context.AddToCar(car);
 
    else if (e.Action == NotifyCollectionChangedAction.Remove)
        foreach (Car car in e.OldItems)
            context.DeleteObject(car);
}

This is standard code for collection change, and the only thing you ever gonna change in it are the names of the entities you want to bind to your DataGrid.
It will collect all changes ever made to your entities.
Now, to commit everything back to the database, all you have to do is add a “Save” button, and give it a very simple handler:

private void saveButton_Click(object sender, RoutedEventArgs e)
{
    context.SaveChanges();
    BindData();
}

That’s it!
The DataGrid itself takes care of assigning the values as they are changed by the user. So all you have to do is just save the changes, and rebind the grid as new IDs are assigned.
Only thing we have left to do now is let our user pick CarMakers off a drop-down list.
If we were writing a WinForms application, it would have been a huge pain in the ass. Luckily, in WPF we have DataGridComboBoxColumn, which makes this job as simple as writing HelloWorld!
All you’ve got to do is create a static property to your form that will return a list of available car makers:

public static List<CarMaker> CarMakers
{
    get
    {
        if (System.Diagnostics.Process.GetCurrentProcess().ProcessName == "devenv")
            return new List<CarMaker>();
 
        return context.CarMaker.ToList();
    }
}

Noticed the weird “if” statement?
It is needed to make sure we are not in Designer Mode.
Without it, whenever you try to open your form in Designer Mode, it will throw exceptions as it has no DataContext to load the data from.
Once we have this, replace the “ReadOnly” CarMaker column with:

<DataGridComboBoxColumn Header="Car Maker" ItemsSource="{x:Static my:MyForm.CarMakers}" SelectedValueBinding="{Binding Path=CarMakerID}" DisplayMemberPath="Name" SelectedValuePath="ID" />

Other than the ItemsSource attribute, everything is quite self-explanatory.
The ItemsSource attribute binds to our static property we have just created, so all you have to do is replace “MyForm.CarMakers” with your form and property name.
There you go!
Now you can bind any Entity Framework data you wish to your WPF DataGrid and then commit it back whenever the users wishes to save his work!

Category: Entity Framework, WPF

Regular Expressions in Entity Framework

Posted on | January 31, 2011 | No Comments

No matter what school of programmers you belong to, I’m pretty sure you’ll agree with me – Regular Expressions are awesome. You can do very complex input validation with them. You can do intricate searches with a single line of code. A few extra-badass programmers even managed flying to the moon through Regexes only.
So why the hell can’t you use them in your queries?
A search in Google for “Entity Framework Regex” or “Entity Framework Regular Expressions” brings no luck, but I still want to use Regexes in my Entity-to-SQL queries.
Even worse, if you came from MySQL background, like me, and are sure that you’ll be able to use the very convenient REGEXP function, you are in for a huge disappointment – there is none like that in SQL Server.
So, how do we query our nice database with those magical Regular Expressions?
First of all, we’ll need to introduce Regex support to our SQL Server, this would of course be done through CLR Functions.
If you are unsure on how to create those, check out this nice tutorial Microsoft so generously gave us: http://msdn.microsoft.com/en-us/library/ms131052.aspx
The code for the function is pretty straightforward:

using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
 
namespace DatabaseFunctions
{
    public class UserDefinedFunctions
    {
        public static readonly RegexOptions Options =
            RegexOptions.Multiline;
 
        [SqlFunction]
        public static SqlBoolean RegexMatch(
            SqlString input, SqlString pattern)
        {
            var regex = new Regex(pattern.Value, Options);
            return regex.IsMatch(input.Value);
        }
    };
}

Deploy the function on your SQL Server, and now you can do stuff like this:

SELECT * FROM dbo.Item WHERE dbo.RegexMatch([Name],'Windows .* Home Edition') = 1

This is all nice and fun, but being able to run queries with regexes is only half job done, we still want to use this function in our Linq to entities queries.
So, first, don’t forget to update your DataModel with the new function, and then you’ll have to map it.
Unfortunately, unlike with Stored Procedures, Entity Framework doesn’t have any inbuilt mechanism to generate this code for you, so you’ll have to do this on your own.
I’d suggest doing so outside the Model’s .designer.cs file, as it might be overridden once you re-generate the model.
What I prefer doing is creating an extension method (In case you are not familiar with the concept, I suggest you read this article: http://msdn.microsoft.com/en-us/library/bb383977.aspx ).
The code for the mapping function would be:

public static class Extensions
{
    [EdmFunction("MyModel.Store", "RegexMatch")]
    public static bool RegexMatch(this string input, string pattern)
    {
        throw new NotSupportedException("Direct calls are not supported.");
    }
}

Note the “EdmFunction” attribute on the function: this is where the magic happens, the body of the function just throws an exception, as it will never be called directly.
Why does Microsoft force us to write this ugly code? I don’t know, but it’s the only way to make it work.
Anyway, the EdmFunction attribute takes two parameters:
•    NamespaceName – which should always be in the format [Your model's name].Store
•    FunctionName – which is quite self-explanatory
Once you have done that, the road is clear to write Queries such as the next one:

var item = (from i in context.Item
            where i.Name.RegexMatch("Windows (.*) Home Edition")
            select i).ToList();

Congratulations! Now you can write LINQ2Entity queries that search for Regex patterns!