LINQ to SQL Tips and Tricks

• Download source - 524.98 KB

Introduction

In this article, I'll demonstrate some tips and tricks I found while working with LINQ.

Source Code

The source code contains web applications with code samples for all tips and tricks described in the article. For running the application, you'll need AdventureWorks database that can be downloaded here. Also you'll need to update the connection string in the configuration file with proper values.

To keep the examples simple, I imported only 3 tables to the LINQ model. Below is the model schema:

LINQ to SQL Tips and Tricks

Load Options

By default, when you load an entity all associations are not loaded. By default, all associations are lazy-loaded or in other words are loaded only when are required/accessed. Let’s examine the following code:

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    //First query is executed. The query returns one product.
    Product product = db.Products.Where(o => 
	o.ProductSubcategoryID != null).FirstOrDefault();

    //Second query is executed. The query returns 
    //the subcategory for the previous product.
    if (product.ProductSubcategory != null)
    {                   
        return product.ProductSubcategory.Name;
    }

    return "";
} 

As you can see, when ProductSubcategory is accessed, a second query is taking place. This is called lazy loading. For eliminating this, you can use load options. Load options are guiding LINQ what associated data needs to be loaded for some entity. The next example is using load options.

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    DataLoadOptions loadOptions = new DataLoadOptions();               
    loadOptions.LoadWith<Product>(o => o.ProductSubcategory);
    db.LoadOptions = loadOptions;
   
    //One query is executed. The query returns one product 
    //and its subcategory using a left outer join
    Product product = db.Products.Where(o => 
	o.ProductSubcategoryID != null).FirstOrDefault();

    if (product.ProductSubcategory != null)
    {
        return product.ProductSubcategory.Name;
    }

    return "";
}

Please notice that only one query is executed in the second example.

Deferred Loading

Deferred loading or lazy-loading can be disabled or enabled on the data context by setting DeferredLoadingEnabled property. The default value is true so all the associations will be lazy-loaded. Let’s see an example when deferred loading is disabled:

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    db.DeferredLoadingEnabled = false;

    //First query is executed. The query returns one product.
    Product product = db.Products.Where(o => 
	o.ProductSubcategoryID != null).FirstOrDefault();

    //ProductSubcategory will always be null. If DeferredLoadingEnabled would
    //be true then this would cause a second query to the database and 
    //ProductSubcategory would receive a value
    if (product.ProductSubcategory != null)
    {
        //this will never be executed
        return product.ProductSubcategory.Name;
    }

    return "";
} 

There is an error that’s often happening when deferred loading is enabled. When you try to access an association and the data context object was disposed already, you will get “Cannot access a disposed object” exception.

Product product = null;

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    db.DeferredLoadingEnabled = true;//this is the default value

    product = db.Products.Where(o => o.ProductSubcategoryID != null).FirstOrDefault();
}
//this will raise an error - Cannot access a disposed object.
return product.ProductSubcategory.Name;

This problem can be fixed by setting DeferredLoadingEnabled equal to false.

Building your Query Step by Step

When working with LINQ, it’s very important to understand when your query is actually executed. The general rule is that the query is executed when you trying to access query data. As you saw in the previous examples, FirstOrDefault() is causing a call to the database because query results have to be read and assigned to Product object properties. However if you don't access the data, you can keep modifying your query without any database calls to be executed. This gives you the possibility to build different queries for different conditions.

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    var products = db.Products.Where(o => o.ProductSubcategoryID != null);

    switch (caseNumber)
    {
        case 1:
            products = products.OrderBy(o => o.ProductNumber);
            break;
        case 2:
            products = products.Where(o => o.ProductModelID != null);
            break;
    }

    /*
     The query will be executed only here and will look like this(for caseNumber  = 1):
        SELECT TOP (1) [t0].[ProductID], [t0].[Name], 
	[t0].[ProductNumber], [t0].[MakeFlag], [t0].[FinishedGoodsFlag],
        [t0].[Color], [t0].[SafetyStockLevel], 
	[t0].[ReorderPoint], [t0].[StandardCost],
        [t0].[ListPrice], [t0].[Size], [t0].[SizeUnitMeasureCode], 
	[t0].[WeightUnitMeasureCode], [t0].[Weight],
        [t0].[DaysToManufacture], [t0].[ProductLine], [t0].[Class], [t0].[Style],
        [t0].[ProductSubcategoryID], [t0].[ProductModelID], 
	[t0].[SellStartDate], [t0].[SellEndDate], [t0].[DiscontinuedDate],
        [t0].[rowguid], [t0].[ModifiedDate]
        FROM [Production].[Product] AS [t0]
        WHERE [t0].[ProductSubcategoryID] IS NOT NULL
        ORDER BY [t0].[ProductNumber]
     */
    Product product = products.FirstOrDefault();

    return product.ProductNumber;
}

As you can see, the resulting query is looking as expected for case 1.

Dynamic Queryable

DynamicQueryable is a class that allows you to build queries that accept string arguments. This could be very useful when for example you need to do an “order by” operation and you get the column name in form of a string.

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    Product product = db.Products.Where
	("ProductSubcategoryID != null").OrderBy("ProductNumber").FirstOrDefault();
    return product.ProductNumber;
}

You can find this class inside this solution or download it from Visual Studio 2008 samples page here.

LINQ Objects and Web Applications

By default, if your application is using in-process state management, you'll be able to store LINQ objects in Session, Application and Cache. However you won't be able to store them in the view state. The reason is that the data stored in the view state is serialized using a binary formatter. To make the LINQ class serializable, you'll need to mark all System.Data.Linq.EntitySet and System.Data.Linq.EntityRef fields with NonSerialized attribute and to mark with Serializable attribute the LINQ class.

One more solution is to manually extract data from the LINQ objects, store it to some intermediate format (class, struct,... etc.) and later manually transform it back.

One more solution is to use Json.NET library.

• http://james.newtonking.com/projects/json-net.aspx
• http://www.codeplex.com/json/

This library allows you to do bidirectional transformation of LINQ objects to their JSON representation. For example, this code will allow you to persist the product in the view state:

Product product = ... get product
ViewState["product"] = JsonConvert.SerializeObject(product);
product = JsonConvert.DeserializeObject<Product>(ViewState["product"] as string);

LINQ Objects and Web Services

XML serialization is used when returning data from the web service. In most of the cases, returning LINQ objects from the web service will fail because of the circular references presented in the model. Basically the XML Serialization will fail for any object that is referenced or is referencing another object. You can use the same workaround as in the previous examples. However you can use without problems LINQ generated classes as types for the input parameters of the web service.

Attach Method

When working with LINQ, most probably you'll want to pass LINQ objects around different method and classes. The problem is that you can't simply take LINQ objects created in one data context and use them in another data context. To be able to use them, you need to attach them to the context. Here is when you should use the attach method. Also the method allows you to update entities that have changed outside the context. Below is an example that demonstrates this:

ProductCategory changedProductCategory = null;

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    db.DeferredLoadingEnabled = false;
    changedProductCategory = db.ProductCategories.FirstOrDefault();
}

string json = JsonConvert.SerializeObject(changedProductCategory);

//re-create the object
changedProductCategory = JsonConvert.DeserializeObject<ProductCategory>(json);

//change modified date
changedProductCategory.ModifiedDate = DateTime.Now;


ProductCategory originalProductCategory = null;

//create second context to get original entity
using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    originalProductCategory = db.ProductCategories.Where(o => o.ProductCategoryID == 
	changedProductCategory.ProductCategoryID).FirstOrDefault();
}

//create third context to do the update
using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    db.ProductCategories.Attach(changedProductCategory, originalProductCategory);

    //Here an update query is executed and ModifiedDate is updated in the database. 
    //Notice that the column that changed is determined automatically.
    db.SubmitChanges();
} 

Please notice that in order for this attach method to work, all entities passed to the method should be created outside the current data context.

Translate

This is a very useful method that allows you to translate a DBDataReader to a list of LINQ objects.

IEnumerable<ProductCategory> prodCatLst = db.Translate<ProductCategory>(reader);

Using DATEDIFF and LIKE in LINQ

If you need to use SQL Server DATEDIFF function or LIKE functionality, you need simply to use the methods of System.Data.Linq.SqlClient.SqlMethods class in your LINQ query. Bellow is an example of using the methods:

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    /*
     This will produce the following query:
        SELECT [t0].[ProductCategoryID], [t0].[Name], 
		[t0].[rowguid], [t0].[ModifiedDate]
        FROM [Production].[ProductCategory] AS [t0]
        WHERE [t0].[Name] LIKE '%ike%'                
     */
    ProductCategory pc = db.ProductCategories.Where(o => 
	SqlMethods.Like(o.Name, "%ike%")).FirstOrDefault();

    /*
      This will produce the following query:
        SELECT TOP (1) [t0].[ProductCategoryID], 
	[t0].[Name], [t0].[rowguid], [t0].[ModifiedDate]
        FROM [Production].[ProductCategory] AS [t0]
        WHERE DATEDIFF(Year, [t0].[ModifiedDate], @p0) > 1                
    
        Where @p0 is equal to DateTime.Now value we passed
     */
    pc = db.ProductCategories.Where(o => 
	SqlMethods.DateDiffYear(o.ModifiedDate, DateTime.Now) > 1).FirstOrDefault();
}  

Conclusion

Thank you for reading. I hope this was useful to you.

History

• 5^th December, 2009: Initial post