DevLog

There is a very good resource on LINQ design guidelines that you can read more on it here:

LINQ Framework Design Guidelines (http://blogs.msdn.com/mirceat/archive/2008/03/13/linq-framework-design-guidelines.aspx)

Hope it helps,
Regards

Tags: DLinq, Linq, XLinq

I figured out tonight something new in LINQ To SQL related to updating records in the database and here it is:

Suppose you have a table in SQL Server 2005, that stores data related to Article. Each article has the ArticleID, Body, Abstract, title, etc ... Also, the Body column has AllowNull set to false, so it is a must to always have a value for the Body column.

When I open the web form used to update the article, and then I change the value of the Body field into "null", that is I simply delete the body value from the HTML Editor I am using (FCKEditor). Then i press Update, everything goes fine, but then i notice that the Body column in the table is still the old value and not the "null".

What happened here is that, LINQ To SQL will check if the value for a column, who has AllowNull is false, is null, then the old value of the column will be preserved. However, if I update the value of the Body field to another value different from null, then the Body column is being updated sucessfully!!

Hope this feature helps you out while working on LINQ To SQL.

Regards

Tags: DLinq, Linq

In a previous post of mine (Deferred Loading in DLinq - https://bhaidar.net/cs/archive/2007/07/26/deferred-loading-in-dlinq.aspx), I explained to you how deferred loading works in DLinq. There have been some changes to the object used to enable/disable deferred loading in Orcas Beta 2! The new object used is now called System.Data.Linq.DataLoadOptions.

Suppose you have two tables: Customer and Orders from the Northwind database. Now suppose you executed this query:

var getCustomers= from c in db.Customers select c;

The above query will get only the customer's data from the database as this SQL Statement show:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]

From the above query, it seems that the Orders records was not retrieved. This means, if you were binding this collection of objects to a GridView, and you have a column that repsents the OrderID of each order done by a customer, this means we will have to access the database with the number of customers bound to the GridView + 1 (representing the first query to get Customers). And this is really bad!

The solution is by diabling Deferred loading and asking the Linq To SQL to get the associated objects as follows:

NorthwindDataContext db= new NorthwindDataContext("...");
db.LoadOptions= new System.Data.Linq.DataLoadOptions().LoadWith<Customer>(c => c.Orders);
var query= from c in db.Customers select c;

Linq To SQL will generate the following query:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
    SELECT COUNT(*)
    FROM [dbo].[Orders] AS [t2]
    WHERE [t2].[CustomerID] = [t0].[CustomerID]
    ) AS [count]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

As you can see, the above Linq statment forced the query to get customers to load with it the information about the orders of each customer. All this is done in one database access,

Hope this helps you,
Regards

Tags: DLinq, Linq

I was practicing a little bit on using Linq to Sql, and had this scenario:

Customers and Orders tables in Northwind Database. I added a new customer with no records, I wanted to apply a left outer join to get customers whose CustomerID starts with letter B such that, even if the Customer had no record in the Orders table, I wanted his/record to be present in the result.

The easiest way to go is to use LEFT OUTER JOIN between Customers' table and Orders' table so that I get all Customers that pass the filter expression and even have no records in the Order table, here is the Query Expression:

        NorthwindDataContext db= new NorthwindDataContext();
        var query=  from c in db.Customers
                    where c.CustomerID.StartsWith("B")
                    join o in db.Orders on c.CustomerID equals o.CustomerID into sr
                    from x in sr.DefaultIfEmpty()
                    select new {CustomerID= c.CustomerID, ContactName=c.ContactName, OrderID = x.OrderID == null ? -1 : x.OrderID};   

As you can see I used the join into operator in Query Expression which results in the LEFT OUTER JOIN, I added my filter expression in the where clause and finally, I returned an anonymous type containing CustomerID, ContactName, and OrderID which is -1 in case there is a null Order record for the Customer otherwise returns the OrderID.

The T-SQL representation of the above is as follows:

SELECT [t0].[CustomerID], [t0].[ContactName],
    (CASE
        WHEN [t1].[OrderID] IS NULL THEN @p1
        ELSE [t1].[OrderID]
     END) AS [value]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
WHERE [t0].[CustomerID] LIKE @p0

Notice how the LEFT OUTER JOIN appears, notice how the CASE appears which reflect the if/else that I have added in the above Query Expression.

Hope this helps you!
Regards

Tags: C# 3.0, DLinq, Linq

I would like to invite you to check this wonderful website dedicated to LINQ (XML, Objects, database, Dataset, etc ...)

www.hookedonlinq.com

Make sure you visit it!
Regards

Tags: DLinq, Linq

Dlinq handles optimistic concurrency implicitly when exexuting SubmitChanges() method. However, you can handle the resolution of the conflict and deciding on how to solve a conflict that happened.

Usually, when two users retrieve the same record, one of them updates the record and submit changes, then anotehr user comes in and performs another update, here the conflict is detected.
Here is an example:

    Product product = db.Products.First(p => p.ProductID == 1);
   

    // Open a second connection to the database to simulate another user
    // who is going to make changes to the Products table               
    Northwind otherUser_db = new Northwind(connString) { Log = db.Log };
    Product otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1);
    otherUser_product.UnitPrice = 999.99M;
    otherUser_product.UnitsOnOrder = 10;
    otherUser_db.SubmitChanges();

    // first user re-updates the UnitPrice
    product.UnitPrice = 777.77M;

    bool conflictOccurred = false;
    try {
        db.SubmitChanges(ConflictMode.ContinueOnConflict);
    } catch (OptimisticConcurrencyException ex) {
        Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *");
        foreach (OptimisticConcurrencyConflict aConflict in ex.Conflicts) {
            Product prod = (Product)aConflict.Object;
            Console.WriteLine("The conflicting product has ProductID {0}", prod.ProductID);
            Console.WriteLine();
            Console.WriteLine("Conflicting members:");
            Console.WriteLine();
            foreach (OptimisticConcurrencyMemberConflict memConflict in aConflict.GetMemberConflicts()) {
                string name = memConflict.MemberInfo.Name;
                string yourUpdate = memConflict.CurrentValue.ToString();
                string original = memConflict.OriginalValue.ToString();
                string theirUpdate = memConflict.DatabaseValue.ToString();
                if (memConflict.HaveModified) {
                    Console.WriteLine("'{0}' was updated from {1} to {2} while you updated it to {3}",
                                      name, original, theirUpdate, yourUpdate);
                } else {
                    Console.WriteLine("'{0}' was updated from {1} to {2}, you did not change it.",
                        name, original, theirUpdate);
                }
            }
            Console.WriteLine();
        }
        conflictOccurred = true;
    }

The above example shows how the conflict is detected and displays a detailed information on all the conflicting members.

Inside the catch statement, we can resolve the conflict as follows:

RefreshMode.OverwriteCurrentValues
-------------------------------------
catch (OptimisticConcurrencyException ex) {
        ex.Resolve(RefreshMode.OverwriteCurrentValues);
        db.SubmitChanges();
    }

With OverwriteCurrentValues, the second user who tried to update the record, his/her update will be disgarded and the record in the database will be maintained!

RefreshMode.KeepCurrentValues
-------------------------------------
catch (OptimisticConcurrencyException ex) {
        ex.Resolve(RefreshMode.KeepCurrentValues);
        db.SubmitChanges();
    }

With KeepCurrentValues, the second user will override any changes in the database record done by the first user. Any column that the second update didn't change, it will preserve its original data from the database and not from the update done by the first user if any.

RefreshMode.KeepChanges
-------------------------------------
catch (OptimisticConcurrencyException ex) {
        ex.Resolve(RefreshMode.KeepChanges);
        db.SubmitChanges();
    }

With KeepChanges, the second user will override any changes in the database record done by the first user. Any column that the second update didn't change, it will be assigned the value that was updated by the first user.

Hope this post really helps you. I want to post this to make sure I have a small post as reference to keep myself on the safe side not to forget it!!

Regards

Tags: DLinq, Linq

A very nice feature I find is to be able to execute a custom SQL statement in case you find the functionalities provided by the DataContext object is limited.

There are two main methods:

ExecuteQuery, which has two flavors of the ExecuteQuery method

1- Without parameters
 IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
  @"select c1.custid as CustomerID, c2.custName as ContactName
   from customer1 as c1, customer2 as c2
   where c1.custid = c2.custid"
 );

We are executing a T-SQL statement by passing the query to the ExecuteQuery method. The return type if a collection of Customer classes.

2- With Parameters
 IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
  "select contactname from customers where city = {0}",
  "London"
 );

As you can see above, we are passing the City parameter to get a parameterized query.

There is important note here which is that, ExecuteQuery is capable of materializing the results into a collection of objects as long as the returned columns match the corresponding
entity class' columns.

Also, you can execute a command against the database using the ExecuteCommand as follows:

    db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");

In the above command, we are updating the UnitPrice of the Products table.

Hope this helps,
Regards

Tags: DLinq, Linq

There are two ways in DLinq to work with Transactions. One is preferred to another.

1. Using the TransactionScope class - Preferred One

using(TransactionScope ts = new TransactionScope()) {
 db.SubmitChanges();
 ts.Complete();
}

2. Using the BeginTransaction on the SqlConnection

Product prod = q.Single(p => p.ProductId == 15);
if (prod.UnitsInStock > 0)
 prod.UnitsInStock--;

db.Transaction = db.Connection.BeginTransaction();
try {
 db.SubmitChanges();
 db.Transaction.Commit();
}
catch {
 db.Transaction.Rollback();
 throw;
}
finally {
 db.Transaction = null;
}

Hope this helps,
Regards

Tags: DLinq, Linq

As you know every object retrieved using Linq to SQL is being tracked by the DataContext class for any changes that happen in the life time of the object in the application. However, this takes more processing and off course performance will be affected.
Suppose one time you want to load some records from the database and bind them to a data control without the need to do any changes, instead we only want to load the records and show them on a a web page or windows form.

Here we need to tell the DataContext class not to track changes on the following query that is to be excecuted:

// Disable tracking
db.ObjectTracking = false;

// Prepare a query expression
var q = db.Customers.Where( c => c.City = "London");

// Execute the query
foreach(Customer c in q)
 Display(c);

As you can see this can save some performance in case you are loading lots of records!

Hope this helps,
Regards

Tags: DLinq

In an API I use in my applications at work, we have the notion of StoredQuery. We prepare a query against the database, store it as a StoredQuery, then execute the query. Why do we need to save the query expression? Maybe at some other time, we need to execute the same query, off course not neceessarily same results. So here the StoredQuery comes in handy!
I found today that DLinq has the same notion of saving or compiling a query for later use. How do you create a compiled query? Here is a sample:

static class Queries
{
 public static Func<Northwind, string, IQueryable<Customer>> CustomersByCity =
  CompiledQuery.Compile((Northwind db, string city) =>
   from c in db.Customers where c.City == city select c);
}

Now you have created a compiled query, you can notice that the Func delegate takes as input the DataContext, the city, and the result is IQueryable of customers.

How to use the above compiled query?

public IEnumerable<Customer> GetCustomersByCity(string city) {
 Northwind db = new Northwind();
 return Queries.CustomersByCity(myDb, city);
}

You define a new method, inside it you create a DataContext instance, and finally you call the compiled query!

Hope this helps,
Regards

Tags: DLinq, Linq