LINQ to SQL Basics – Part 5 (Extension Methods)

If you try to use an extension method within LINQ to SQL you will run across this error:

This is exactly what happened to me. Let me take you through the steps of what I did and how I resolved it.

Say you have the below mentioned LINQ query.

You want to print out all the Customer names, but the all names in the database has different casing. A problem we developers usually have. So what you want to do is to display it in proper case. We would want to re-use this functionality in other places to. So the first thing that comes to mind is to create an extension method. Something that allows us to say String.ToTitleCase() as shown below.

So let us get to work. I added a new class file to my solution called extensions.cs with the following code:

This method will now allow me to use this with any string. See below example:

Now let’s use this new functionality within our LINQ query:

When we run our application now we get the following error:

That doesn’t seem to work. The reason for this now working is because LINQ to SQL doesn’t know how to translate the method “ToTitleCase()” into SQL code. It sees it as part of the LINQ expression. To get around this we need to create a partial method on our Customers data entity. All our LINQ to SQL entities consists out of partial classes. So we can implement another partial class on our existing classes to extend our functionality. There is different ways of implementing these classes by making use of different events in these classes, but I will just focus on a simple solution for now. So what you need to do is to add another class file to your project. We are going to extend the functionality of your existing LINQ to SQL entities. We don’t want our changes to be overwritten when we regenerate our data context. I added a new class file called Northwind_Extend.cs. I want to create a partial class of the Customer class. So i am going to create it exactly as it is within my data context using exactly the same interfaces:

What I am going to do now is to create a new property for CompanyName called CompanyNameProper. The reason why I have done this is because I don’t want to update my DB with the data formatted proper case. So this is what I have done next:

We also need to change our LINQ query to make use of the new property:

So when we run our application now, it works as it should!

LINQ to SQL Basics – Part 4 (Lambda Expressions)

Today I will be showing a few short examples of using Lambda expressions. Lambda expressions is nothing new, it also exists in other languages and been used for quite a while. Lambda expressions are based on Lambda Calculus. You can do some research on Lambda Calculus on Wikipedia (www.wikipedia.org). I recommend reading Chapter 5 (Understanding Lambda Expressions and Closures) of SAMS LINQ Unleashed for C# by Paul Kimmel.

By using Lambda expressions we can write our LINQ queries more compact. We all love less code, don’t we?

Look at the following simple select:

var q = from ct in db.Customers

select ct;

can be written using a Lambda expression as follow:

var q = db.Customers.Select(ct => ct);

The same SQL query is constructed for both of the selects. Now let’s build the query up a bit. We adding a ‘Where’ to the select statement. This is how it will look now:

var q = from ct in db.Customers

where ct.CustomerID == “ALFKI”

select ct;

This is how the Lambda expression will look like:

var q = db.Customers.Where(ct => (ct.CustomerID == “ALFKI”));

Lambda expressions can get quite complicated the more complex your query gets. Let’s look at the next query. Here we are selecting all the orders for CustomerID = “ALFKI”:

var f = from ct in db.Customers

join o in db.Orders

on ct.CustomerID equals o.CustomerID

where ct.CustomerID == “ALFKI”

select o;

Now writing this as a Lambda expression gets more involved:

var g = db.Customers

.Where(ct => ct.CustomerID == “ALFKI”)

.Join(db.Orders, ct => ct.CustomerID, o => o.CustomerID, (ct, o) => o);

Using Lambda Expressions does take some time to understand and grow on you.

 

 

LINQ to SQL Basics – Part 3 (Select with “lazy loading”)

I will be using the Northwind database in this example.

Say you get the following requirements:

Show me all the Customers which ordered more than 5 items of Tofu between ’1998-03-06′ and ’1998-06-06′, and the Supplier which supplied the product.

Decoding the above requirements we will see that we need data from quite a few statements. So let’s see how the SQL select statement would look like for this:

select distinct ct.CompanyName CustomerName, s.CompanyName SupplierName

from [Order Details] od

inner
join Orders o

on o.OrderID = od.OrderID

inner
join Products p

on od.ProductID = p.ProductID

inner
join Suppliers s

on p.SupplierID = s.SupplierID

inner
join Customers ct

on ct.CustomerID = o.CustomerID

where o.OrderDate between
’1998-03-06′
and
’1998-06-06′

and p.[ProductName] =
‘Tofu’

and od.Quantity > 5

Quite a long and lumpy join… Something called “lazy loading” in LINQ make this big lumpy select a little bit simpler. You have instant access to the data that is joined to a specific table. Let’s have a look at how we could do this in LINQ.

var q = (from od in nw.Order_Details

where od.Order.OrderDate > DateTime.Parse(“1998-03-06″)

&& od.Order.OrderDate < DateTime.Parse(“1998-06-06″)

&& od.Product.ProductName == “Tofu”

&& od.Quantity > 5

select
new

{

CustomerName = od.Order.Customer.CompanyName,

SupplierName = od.Product.Supplier.CompanyName

}).Distinct();

As you can see we aren’t joining Order_Details up with any other tables. Let’s have a closer look using SQL profiler to see the actual SQL created:

exec
sp_executesql
N’SELECT DISTINCT [t3].[CompanyName] AS [CustomerName], [t4].[CompanyName] AS [SupplierName]

FROM [dbo].[Order Details] AS [t0]

INNER JOIN [dbo].[Orders] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]

INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t0].[ProductID]

LEFT OUTER JOIN [dbo].[Customers] AS [t3] ON [t3].[CustomerID] = [t1].[CustomerID]

LEFT OUTER JOIN [dbo].[Suppliers] AS [t4] ON [t4].[SupplierID] = [t2].[SupplierID]

WHERE ([t1].[OrderDate] > @p0) AND ([t1].[OrderDate] < @p1) AND ([t2].[ProductName] = @p2) AND ([t0].[Quantity] > @p3)’,N’@p0 datetime,@p1 datetime,@p2 nvarchar(4),@p3 int’,@p0=’1998-03-06 00:00:00′,@p1=’1998-06-06 00:00:00′,@p2=N’Tofu’,@p3=5

Mmmmm…. Two outer joins…. Not what we wanted. Lets us try to change our LINQ query that it doesn’t produce the outer joins. To get past the outer joins we will have to remove some of the “lazy loading”. So the LINQ query will look as follow:

var q = (from od in nw.Order_Details

where od.Quantity > 5

&& od.Order.OrderDate > DateTime.Parse(“1998-03-06″)

&& od.Order.OrderDate < DateTime.Parse(“1998-06-06″)

&& od.Product.ProductName == “Tofu”

join ct in nw.Customers

on od.Order.CustomerID equals ct.CustomerID

join sp in nw.Suppliers

on od.Product.SupplierID equals sp.SupplierID

select
new

{

CustomerName = ct.CompanyName,

SupplierName = sp.CompanyName

}).Distinct();

We still using some of the lazy loading but just joining to Customers and Suppliers to get rid of the outer joins. The SQL query now looks as follow:

exec
sp_executesql
N’SELECT DISTINCT [t3].[CompanyName] AS [CustomerName], [t4].[CompanyName] AS [SupplierName]

FROM [dbo].[Order Details] AS [t0]

INNER JOIN [dbo].[Orders] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]

INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t0].[ProductID]

INNER JOIN [dbo].[Customers] AS [t3] ON [t1].[CustomerID] = [t3].[CustomerID]

INNER JOIN [dbo].[Suppliers] AS [t4] ON [t2].[SupplierID] = ([t4].[SupplierID])

WHERE ([t0].[Quantity] > @p0) AND ([t1].[OrderDate] > @p1) AND ([t1].[OrderDate] < @p2) AND ([t2].[ProductName] = @p3)’,N’@p0 int,@p1 datetime,@p2 datetime,@p3 nvarchar(4)’,@p0=5,@p1=’1998-03-06 00:00:00′,@p2=’1998-06-06 00:00:00′,@p3=N’Tofu’

Just what we want! I recommend that you always use SQL profiler to inspect your queries and make sure that your SQL select statement looks the way that it should!

LINQ to SQL Basics – Part 2 (Creating LINQ to SQL Classes)

I will show you how to create LINQ to SQL data classes within your existing project. I will be using Northwind within my example.

Right click on your project, Add, New Item…

 

Under Visual C# Items, select Data. Under Templates select LINQ to SQL Classes and enter your name for your data context and click Add.

 

Click Server Explorer inside the Northwind.dbml file

In the server explorer tab click the connect to database icon.

 

Setup your database connection and click ok.

 

Now you will be able to add all your tables and stored procedures to your data context. Highlight the tables that you want to add and drag them on your data context. Do the same with the stored procedures.

 

Now we will be able to write LINQ queries…

Add the following using statement on the page where you going to use your LINQ query:

using System.Linq;

I used the following code to test the LINQ functionality in a console app:

using System;

using System.Linq;

 

namespace LINQtoSQLPart2

{

class
Program

{

static
void Main(string[] args)

{

NorthwindDataContext db = new
NorthwindDataContext();

 

var q = from c in db.Customers

select c;

 

foreach (var row in q)

{

Console.WriteLine(“Customer: {0}”, row.CompanyName);

}

Console.ReadLine();

}

}

}

That’s all to it…

LINQ to SQL Basics – Part 1 (Bug?)

I have recently made the jump into LINQ and experienced allot of hours of headaches and pains but working through them. Few things that use to be a drag just seem so much easier now. But more of that in later posts. Let me start off with the basics.

The example in this post is using the Northwind database and executed using LINQPad

Simple things don’t seem so simple anymore using LINQ (hmmm didn’t I just say LINQ makes things easier?).

Let’s consider the following LINQ statement:

Customers ct = new Customers { CustomerID = “AAAAA”, CompanyName = “Aaaaaa” };
Customers.InsertOnSubmit(ct);
SubmitChanges();

Straight forward, we create a new customer with CustomerID = “AAAAA” and CompanyName = “Aaaaa”. Set so that when we submit the changes that the customer is inserted. Then submit the query to the database. The following SQL is created to do the insert:

INSERT
INTO
[Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)

– @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [AAAAA]

– @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Aaaaaa]

– @p2: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p4: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p5: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p6: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p7: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p8: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p9: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

Not too shabby you might think. Now let’s complicate things a bit. Run the following script on your database:

ALTER
TABLE dbo.Customers ADD

CreateDate datetime
NOT
NULL
CONSTRAINT DF_Customers_CreateDate DEFAULT
getdate()

GO

ALTER
TABLE dbo.Customers SET (LOCK_ESCALATION
=
TABLE)

GO

This script creates a new column on the Customers table called CreateDate with a default of getdate.

Ok, now run the below insert script on your SQL database:

insert
into Customers(CustomerID, CompanyName)

values(‘BBBBB’,
‘Bbbbb’)

go

Cool!! (1 row(s) affected) What else did you suspect? J

Let’s do the same in LINQ… Run the below statement in LINQPad:

Customers ct = new Customers { CustomerID = “CCCCC”, CompanyName = “Ccccc” };
Customers.InsertOnSubmit(ct);
SubmitChanges();

Mmmmm!!! What this?!?!… It doesn’t work…

The following error is displayed:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

Let’s have a look at the SQL generated:

INSERT
INTO
[Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [CreateDate])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)

– @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [CCCCC]

– @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Ccccc]

– @p2: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p4: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p5: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p6: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p7: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p8: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p9: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

The createdate field is also included in the insert statement and no value for @P11. This is a bit weird! LINQ up to its many tricks again, which it has up its sleeve. But let’s go on and see what should be done to get past this.

So all we do is basically just included createdate in the LINQ statement:

Customers ct = new Customers { CustomerID = “CCCCC”, CompanyName = “Ccccc”, CreateDate = Datetime.Now };
Customers.InsertOnSubmit(ct);
SubmitChanges();

Now it’s hunky-dory!

I did a bit of further investigation. Created a new field (Varchar(50), Nullable, Default value=”me”). Did an insert from SQL and the default value was set, but from LINQ the field stayed null. So from this I came to the following conclusion: LINQ ignores all the default value’s and binding’s setup on your database!

Hopefully someone might be able to prove me wrong on this. Am I missing something?

First Blog!

Hi There,

Welcome to my new Blog!

I will be blogging something here soon. Watch this space!

For the interim please visit my site and send me any comments related to my site.

RedLeaf: