DevLog

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

The VS 2008 Orcas Beta 2 has been released! Read more about it here:

http://weblogs.asp.net/scottgu/archive/2007/07/26/vs-2008-and-net-3-5-beta-2-released.aspx

 

Regards

Tags:

Applying method filtering on the results of a DLinq query is not available. For example, suppose you want to run method named Validate on the Customer ID before returning it in the query:

var result= from c in db.customers
                select new {CustomerName= c.Name, ID= Validate(c.ID)};

This is not allowed, DLinq will not understand the meaning of Validate in the context of the above query expression. What you do is split the above query into two other queries as follows:

var result1 = from c in db.customers
                   select new {CustomerName= c.Name, ID=c.ID};

va result2= from c in result1.AsEnumerable()
                 select new {CustomerName= c.CustomerName, ID= Validate(c.ID)};

Notice the presence of AsEnumerable() which tells DLinq to treat the second query as a local one and not as a query that should be later on processed on the database. Also, keep in mind that, even with the use of AsEnumerable, still the first query is not yet executed!! It is till deferred.

But at least, AsEnumerable allows you to apply your custom methods on the results without having to deal with any problems with DLinq, by simply shifting the processing of the query from the DLinq to a locally executed one!

Hope this helps,
Regards 

Tags: DLinq, Linq

Suppose we have an entity class called Customer and it contains a property of type List of Order entity classes. Now we want to retrieve all the customers and their orders, how do we do this using Query Expression?

var results=  from c in db.Customer
  select c;

However, this will not load the orders inside the customer entities returned! Why? The reason is that DLinq uses deferred loading and is known as "lazy loading".
In the above query, we only retrieved the customer entities, but the Orders were not loaded too! This goes back to the fact that Customer class has a relation with the Order class just as the relation between Customer and Order data tables. in the database. This explains that related objects are governed by "Deferred loading". Only the main class's properties are loaded, and all related objects are not.

But, when you enumerate through the results, and when you access the Orders property, DLinq goes to the database and retrieves them without letting you know of this!

So, what if we wanted to disable Deferred Loading? You could do a projection and retrieve both elements, but then the result of the projection will be read only. This is where we use DataShape object!!

 DataShape ds = new DataShape();
 ds.LoadWith<Customer>(c => c.Orders);
 db.Shape = ds;
 var q =
  from c in db.Customers
  where c.City == "London"
  select c;

You are informing the query to load the Orders collection on the Customer class, whenever a customer entity is loaded from the database!

Hope this helps,
Regards

Tags: DLinq

I have found out that DLinq internally uses Object Pooling!

Usually, when you retrieve a record from a Database Table, and then retrieve it again, another version is being created for you. This doesn't hold true for C# or VB.NET objects. When  you create an instance of an object, and then request it again, the same instance is brought back (for sure if GC didn't remove it).

With DLinq, when a record is retrieved from the database it is bound or linked to a C#/VB.NET object, its Primary Key is used to store that instance of the object created. So that next time, when you request same Row from the database, a check is made into the store of the "objects pooled" against instance that is mapped to the request Row based on the Primary Key, if the instance is present, then it is retrieved and returned back, else a new instance of the object is created by accessing the database.

Off course, when an update is made to a row, the instance is removed from the pool to maintain integrity of data!

That is really cool!
Regards

Tags: DLinq

In the previous post, I have explained the idea behind Deferred Execution. What if you wanted to directly execute the query expression and bind the results to a GridView or any other Data Control? In this case, Deferred Execution is not required. The solution is simply using one of the methods defined on the Sequence class:

1. ToArray();
2. ToList();

and now the previous query expression is as follows:

var getCustomers= (from c in db.Customers
                             select c).ToList();

We don't have Deferred Execution anymore and the getCustomers now include all the records.

Hope this helps,
Regards

Tags: DLinq, Linq

I have started recently working on C# 3.0 Enhancements and LINQ project at the same time. One of the nice features of LINQ is the ability to write Query Expressions. You can think of Query Expressions as a high-level SQL Query. You write a query as follows:

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

The above is simply a Query Expression that is stored in-memory, and not yet executed! When it is time to execute such a query, it will be retrieved from the memory, translated into a SQL expression, then executed!

If you want to check the results, you have to execute the query right?
To execute a Query Expression, you have to enumerate through the getCustomers query which is of type IQueryable<Customer>.

foreach (var cust in getCustomers)
{
    Console.WriteLine("Customer ID: {0}", cust.CustomerID);
}

It is only when you loop through the result-set you are actually executing the query!! This is what is known by Deferred Execution.

Hope this helps,
Regards

Tags: DLinq, Linq