LINQ To SQL Examples

I have recently been using LINQ to accomplish much of my data abstraction and have found it very useful for building a robust ASP.Net application. Ultimately what LINQ to SQL does is it translates your C# code into a SQL query and returns an object or an object list instead of a dataset. This can be much easier to deal with than a dataset. I have run into a couple things that I didn’t expect so I thought I’d share.

Inner Join with a ‘Where’ clause

Usually when writing a SQL statement you start out with ‘SELECT’ then define what data to select, then you have ‘FROM’ clause in which you define where to get the data, then, if you have a ‘JOIN’ you would define your join and what tables to join. After that you would define your ‘WHERE’ clause and specify how you want to filter this data so it would look something like this:

SELECT a.col1, a.col2, b.col1, b.col2
FROM table1 a
INNER JOIN table2 b ON a.ID = b.ID
WHERE a.col1 > 0

Not so with LINQ to SQL. Ultimately this would be the SQL code sent to the server but your LINQ code starts out with your ‘FROM’ clause, then you specify your ‘WHERE’ criteria, then you specify your ‘JOIN’ statement and last (but not least) you write your ‘SELECT’ statement so it looks more like this:

mydatacontext db = new mydatacontext(connectionstring);

var query = from p in db.table1
             where p.col1 > 0
             join i in db.table2 on p.ID equals i.ID
             select new
             {
                  p.col1,
                  p.col2,
                  i.col1,
                  i.col2
             };

As you can see this is very similar in many was to ANSI SQL standard but in many ways it is quite different.

Outer Join

Our first example shows how to create a query and execute a join, but this is only an inner join. Many times you will need to do an outer join which allows you to to guarantee that you will get all the results from one table and nulls from the other set of joined data if there is no data to be joined. So the SQL equivalent is:

SELECT a.FirstName, a.LastName, b.Product
FROM Persons a
LEFT OUTER JOIN Products b ON a.PersonID = b.PersonID
WHERE a.LastName = 'Smith'

And the LINQ equivalent is:

var query = from a in db.Persons
join b in db.Products on a.PersonID equals b.PersonID into g
from b in g.DefaultIfEmpty()
where p.LastName == "Smith"
select new
{
  a.FirstName,
  a.LastName,
  b.Product
};

What enables us to do this left outer join is the ‘DefaultIfEmpty()’ function which simply returns whatever the default value from the database is if there is no data.

IN clause

A very handy tool in SQL is the ‘IN’ clause that allows you to select data based on multiple criteria without having to have a ton of ‘AND’ conjunctions and criteria. So basically you can specify the following:

SELECT * FROM TABLE WHERE col1 IN ('uno', 'dos', 'tres')

When specifying an IN clause in LINQ you would write the following:

string[] test = new string[] {"uno", "dos", "tres"};
mydatacontext db = new mydatacontext(connectionstring);

var query = from t in db.table
              where test.Contains(t.col1)
              select t;

Notice how you use the C# function ‘Contains’ and this is automatically translated into SQL ‘IN’ clause.

Subqueries

Subqueries are essential to any database developer. Many times you run into situations in which you must get data from one table and calculated data from another table into one object. For instance what if we have a driver’s table which included data on a particular driver such as their name/address and so on. Then we have another table which has raw data that includes their speed in cities, residential areas etc… If you wanted to get the average speed of a driver along with their bio information you could write the following SQL code:

SELECT td.DID, td.FirstName, td.LastName, td.Address,
(SELECT AVG(ts.speed) FROM tblSpeed ts WHERE ts.DID = td.DID AND ts.Location = 'residential') AS avgResidentialSpeed
FROM tblDrivers td

To produce something like this in LINQ you could write the following:

mydatacontext db = new mydatacontext(connectionstring);

var query = from d in db.tblDrivers
              select new
              {
                    d.FirstName,
                    d.LastName,
                    d.Address,
                    avgResidentialSpeed = (from s in tblSpeed
                                                    where s.DID == d.DID
                                                    && s.Location == "residential").Avg()
              };

As you can see you simply nest LINQ queries and the translation to SQL is a subquery.

CASE statements

One way SQL allows you to control flow is through CASE statements. This is translated by LINQ by using a ternary (shorthand) ‘if’ statement. For instance, to get the following SQL statement:

SELECT * FROM TABLE WHERE Col1 = CASE WHEN Col2 = 'A' THEN '1' WHEN Col2 = 'B' THEN '2' END

you would need to write the following LINQ code:

var Query = from p in db.Table
             where Col1 == (Col2 == "A" ? "1" : Col2 == "B" ? "2")
             select p;

This code translates into a case statement in SQL.

Selecting one object

Sometimes you will want to get a single object from a table such as one customer object. To do this you can end your LINQ statement with the ‘FirstOrDefault’ function:

var customer = (from c in db.Customers
             where c.CustomerID == 11356
             select c).FirstOrDefault();

This will return one customer object from the Customers table and you have all the warm fuzzies such as intellisense. The FirstOrDefault() will return null if it does not find any data in the DB. If you need to ensure that you only receive one result you can use Single(), which will return an error if the DB finds more than one result, or if you need to return the first of several results but you want to return an error if there is none then you can use First() function.

Timeouts

Sometimes you may be working with a very complex query that takes a good amount of time to complete. Of course you will always want to ensure that your table structures are setup in an optimal way with indexes etc, but sometimes your app requirements are such that you end up with a very complex query in which case your code may timeout before it receives the response from the database. You can manually control the query timeout time of a LINQ object by going into your LINQ object and hard coding the following:

        partial void OnCreated()
{
this.CommandTimeout = 3600;
}

The default is 300, so this will extend the time that your query can run.

Assembly reference

When migrating your application to a production server you may get an error that says:

Compiler Error Message: CS0234: The type or namespace name 'Linq' does not exist in the namespace 'System.Data' (are you missing an assembly reference?)

The issue here is that your web.config file is not referencing the LINQ assembly. you can add in the reference to the assembly by adding in: