The new ASP.NET (part 3 of 7): Using Linq to query EF

This is part of a series – see this post for explanation of the context: http://robseder.wordpress.com/2010/12/23/the-new-asp-net-part-1-of-7-getting-started/

—–

This blog series is written with the assumption that you are brand new to Linq. Linq is enormous topic. As I’ve been learning – for every question I would answer, I would have 3 more new questions. Linq is a fundamentally different way to work with a programming language.

If you grew up with C (or any of the "curly brace languages") or VB (and it’s derivatives) then you are familiar with "declarative programming". Linq borrows from "functional programming" – which is what the F# language is, by the way. What this means practically is that the calling conventions and language rules you are used to, are changed.

There are tons and tons of articles on the web that explore Linq and go into great deal how it works. For the purpose of this blog series however, I’m just going to focus on the practical parts you need to dig in and be effective, immediately.

From Part 2, we have a database that has referential integrity and have an entity model built around it. Now, in the old days you might do something like this to get data:

    private DataSet GetAllCustomers()

    {

        // TODO: Add some exception handling…

        DataSet dataSet = null;

 

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AtsfCrm"].ConnectionString))

        {

            connection.Open();

            using (SqlCommand command = connection.CreateCommand())

            {

                command.CommandText = "select * from t_Customer where customerID=1 order by FirstName";

                command.CommandType = System.Data.CommandType.Text;

 

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))

                {

                    adapter.Fill(dataSet);

                }

            }

        }

 

        return dataSet;

    }

 

For any select, insert, update, or delete you’d need to have this overhead for doing things. In the end too, you end up with this DataSet blob, which is sort of a late-bound set of data that wasn’t intuitive to work with and was a pain to update.

Querying with Linq:
With Linq, and  especially with using Entity Framework (EF), you can work with this data in a much more intuitive way, with much less code. Here is the equivalent of the above code, using Linq against the EF model we created in Part 2 (where AtsfCrmEntities is the Entity Framework .edmx "model" and t_Customer is a database table defined within):

    private IQueryable<t_Customer> GetAllCustomersViaLinq()

    {

        // TODO: Add some exception handling…

        using (AtsfCrmEntities context = new AtsfCrmEntities())

        {

            IQueryable<t_Customer> query = from customer in context.t_Customer

                                            where customer.CustomerID == 1

                                            orderby customer.FirstName

                                            select customer;

 

            return query;

        }

    }

by the way, you will see that 9 out of 10 samples use the "var" keyword to declare the type "query" above. The argument for this is: "I don’t care about what the Linq query is returning". I argue however that I want to be explicit. Assumptions are the root of just about all bugs in software. I am always explicit with the return of a Linq query!

OK – there are a couple of practical things to understand here. First, the ‘customer’ variable is like a temporary pointer to the "current row" of data, while the Linq query is executing. So, this is similar to a table reference in T-SQL. When this Linq query runs, it’s going to do the where, orderby, and then subsequently "select" the current record to be included in the output.

So what is "query" then? Well, it can either be IEnumerable<T> or IQueryable<T>. Basically, these are like "pointers" to a query to run, but aren’t actually run until you actually try to iterate through the collection. This is significant because the magic of optimization and WHEN EF actually gets the data can be managed by EF.

Iterating through the results:
Practically though, let’s assume you get the general gist of the GetAllCustomersViaLinq() method above – how do you use the output? Well, that’s the amazing part, you can do some things like this:

    foreach (t_Customer customer in GetAllCustomersViaLinq())
    {
        Debug.WriteLine(customer.FirstName + " " + customer.LastName);
    } 

You can work with the data from these tables in a very intuitive way. Even related data. Remember that there is a 1-to-many relationship between t_Customer and t_Job. To get at that referenced data, you don’t need to run another query, it’s automatically attached:

    foreach (t_Customer customer in GetAllCustomersViaLinq())
    {
        Debug.WriteLine(customer.FirstName + " " + customer.LastName);

        foreach (t_Job job in customer.t_Job)

        {

            Debug.WriteLine("-" + job.JobName);

        }

    } 

That keeps going on by the way, for any related information, for example:

    foreach (t_Customer customer in GetAllCustomersViaLinq())
    {
        // Print customer
        Debug.WriteLine(customer.FirstName + " " + customer.LastName);
        foreach (t_Job job in customer.t_Job)
        {
            // Print jobs for this customer
            Debug.WriteLine("-" + job.JobName);
            foreach (t_Project project in job.t_Project)
            {
                // Print projects for this job
                Debug.WriteLine("–" + project.ProjectName);

                // Also work my way back up, and find the customer:
                string customerName = project.t_Job.t_Customer.LastName;
            }
        }
    } 

As you can see you can naturally navigate down into the related data AND navigate back up from your many-to-one relationships. Very cool stuff!

Back to Linq though, here are a couple of the core things you need to know. Above is the basic syntax, but I’ve found that I need a lot of that query to be dynamic, so let’s look at a way to accomplish this.

Where Clause:
The where clause is obviously something that is going to be used a lot. You can basically do a where clause at least two ways. Right in the query:

    IQueryable<t_Customer> query = from customer in context.t_Customer
                                    where customer.LastName == "Seder" 
                                        && customer.FirstName == "Robert"
                                    select customer; 

or you can apply it after you set up the query. Remember, the query doesn’t actually run until someone tries to iterate through the results – so this isn’t like SQL where you wouldn’t want to bring back a zillion rows, then apply the where clause after the fact. In Linq, you are adding "rules" to the query for later on when it is run. Here’s how to do that after-the-fact:

    IQueryable<t_Customer> query = from customer in context.t_Customer
                                    select customer;
 
    query = query.Where(customer => customer.LastName == "Seder" 
        && customer.FirstName == "Robert");

Now you probably just saw that "customer => " and said "What IS that??". This is some of where Linq gets kind of weird. This is a "lambda expression" where you make up a variable name where the type is already implied, and so that you have intellisense and can reference it properly, to apply operations. In other words "LastName" is a property of t_Customer, so you couldn’t just to "LastName==" because that’s treating a foreign property like a variable.

Again, this isn’t where you need to necessarily understand Linq at a deep level (Lord knows I sure don’t) – but hopefully you get the gist of the syntax.

Lastly, what if you need to work with string names and are thinking of writing some shortcut wrappers for this? Well, let’s Look at the "type" of what is applied to a .Where() clause:

image

image

The type is Expression<Func<t_Customer, bool>> or Expression<Func<t_Customer, int, bool>>. The other two overloads are the same, except for IEnumerable<T>. So, you’re thinking that you could just turn those into arguments into your method, and then use the results as a variable – like this:

    public static IQueryable<t_Customer> GetAllCustomersViaLinq(
        Expression<Func<t_Customer, bool>> whereExpression)
    {
        using (AtsfCrmEntities context = new AtsfCrmEntities())
        {
            IQueryable<t_Customer> query = from customers in context.t_Customer
                                            select customers;

            query = query.Where(whereExpression);
            return query;
        }
    }

that means you can at least let the where-clause logic happen earlier in the callstack, without having to cram a bunch of logic into the linq query. That means you now call this method like this, using that same Lambda expression syntax:

    IQueryable<t_Customer> customers =

        GetAllCustomersViaLinq(customer => customer.CompanyName == "ABC Company");

Now, if you want to be able to do: GetAllCustomersViaLinq("CompanyName==’test’) or something like that, lots of people have written some really complicated libraries for things like this, but I am avoiding that. I have opted to hard-code my where-clause logic in code with these strongly-typed objects.

OrderBy Clause:
The good news is OrderBy is very similar to "where" in that it uses that similar Lambda expression syntax. However, OrderBy has a more logical way to express the order-of-operations for the order by. For example:

    public static IQueryable<t_Customer> GetAllCustomersViaLinq()
    {
        using (AtsfCrmEntities context = new AtsfCrmEntities())
        {
            IQueryable<t_Customer> query = from customer in context.t_Customer
                                            orderby customer.CompanyName ascending
                                                    customer.LastName ascending
                                            select customer;
            return query;
        }
    } 

this is even more T-SQL like in this style. You can also do this with explicit method calls too:

    public static IQueryable<t_Customer> GetAllCustomersViaLinq()
    {
        using (AtsfCrmEntities context = new AtsfCrmEntities())
        {
            IQueryable<t_Customer> query = from customer in context.t_Customer
                                            select customer;

           
query = query.OrderBy(
                customer => customer.CompanyName).ThenBy(
                customer => customer.LastName);
            return query;
        }
    }

As you can see, there is an initial OrderBy/OrderByDescending – and then additional clauses are added on with ThenBy/ThenByDescending method calls.

Lastly, again, you may want to abstract this logic away from this core method. If you look at the .OrderBy() call, see what the declaring type is:

image

image

this is where I ran into a brick wall. If you declare your method now like this:

    public static IQueryable<t_Customer> GetAllCustomersViaLinq(
        Expression<Func<t_Customer, string>> orderByExpression)
    {
        using (AtsfCrmEntities context = new AtsfCrmEntities())
        {
            IQueryable<t_Customer> query = from customer in context.t_Customer
                                            select customer;
 
            query = query.OrderBy(orderByExpression);
            return query;
        }
    }

this is where things fall apart. When you try to pass in something like customer=>customer.FirstName and it works. If you pass any non-string, it doesn’t, you get a compile error about converting to a string. If you do a .ToString() on the objects, then it compiles, but at run-time, the UI gets all sorts of weird errors about not supporting a conversion to string.

I spent about a day on trying to understand and play with this. In the end, this just didn’t work. Worse, this would only handle one level of OrderBy anyhow. So instead, I just created a method like this (simplified):

    private static IQueryable<t_Customer> SortResults(IQueryable<t_Customer> query, string sortField, bool? sortAscending)

    {

        if (!sortAscending.HasValue) sortAscending = true;

 

        switch (sortField)

        {

            case "Customer":

                if (sortAscending.Value)

                    return query.OrderBy(customer => customer.CompanyName).

                        ThenBy(customer => customer.LastName).

                        ThenBy(customer => customer.FirstName);

                else

                    return query.OrderByDescending(customer => customer.CompanyName).

                        ThenByDescending(customer => customer.LastName).

                        ThenByDescending(customer => customer.FirstName);

            case "FirstName":

                if (sortAscending.Value)

                    return query.OrderBy(customer => customer.FirstName);

                else

                    return query.OrderByDescending(customer => customer.FirstName);

            // Etc, etc…

        }

    }

Similarly, if you want T-SQL-like orderby syntax, a few people have written some complex parsers and libraries for this. Again, I’m opting to keep this strongly-typed and as simple as I can.

Well there it is. There are more comprehensive studies on Linq and it’s internals, but hopefully this post helps give a quick overview of how to use Linq effectively with Entity Frameworks.

So far, we have a database, an Entity Framework model over it, and now Linq, which is a really powerful feature to query it. All we need now is a user interface – on to MVC next…

Posted in .NET 4.0, ASP.NET, ASP.NET MVC, Entity Framework, Linq, Uncategorized
One comment on “The new ASP.NET (part 3 of 7): Using Linq to query EF
  1. […] The new ASP.NET (part 3 of 7): Using Linq to query EF « Rob Seder's Blog on December 30, 2010 at 10:25 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2 other followers

%d bloggers like this: