I am just starting with linq to sql but I want to do it correctly.

I created 2 class libraries: bll and dal. In the dal I created the mapping for my linq to sql to the northwind database. In the bll I created a method like this:

public Product GetProductById(int id)
{
    NorthWindDataContext dc = new NorthWindDataContext();
    Product result = dc.Products.SingleOrDefault(c => c.ProductID == id);
    return result;
}

where Product is in the namespace, which I included with the using statement, of the dal layer.

When I execute this method I notice that all products are taken from the database and only then a "where" is performed to get that one particular product from the list where the id corresponds. What I want is to only directly retrieve that particular product and don't get the whole list. How should I do this?

This should work fine. How are you determining that you are getting all products from the database? This should just give you a standard T-SQL query with a WHERE clause.

In situations like these I run SQL Server Profiler, which tells me exactly what SQL is being emitted to SQL Server.

 I stopped using the visualiser because it wasn't always giving me the right results. In your case I suspect it is not taking notice of the 'c =>' part properly.

I really do recommend using SQL Server Profiler. It is pretty quick to get going and then you know exactly what is going to SQL Server, completely independently of Visual Studio. Just fire it up, create a new trace, start it with the default values and then run your LINQ query. Then stop the trace and hunt through the events for your query (you may find there are a quite a few events resulting fom general SQL Server chatter... it's quite interesting to see just how much is going on in the background).

 Just not to get swamped by all data. Which settings do you use to monitor in sql profiler?

 I do File / New Trace.

Then I leave template as 'Standard (default)'.

But in the 'Events Selection' Tab I untick 'Audit Logon', 'Audit Logoff' and 'EixistingConnection'.

Once the trace,is running, I pause it and do 'Edit / Clear Trace Window'.

Then I get everything ready to generate the query (e.g., I start my web applicaiton and get ready to go to the page which will generate the query I want to examine).

Then I start the trace.

Then I hit the page which will generate the query.

Then I stop the trace.

Even after doing this I still find there are a few records to look through, but not too many. Perhaps it is possible to filter things even better, but I find doing it this way is okay.

 Hi,

I forgot to mention a wonderful tool I use to help with this sort of thing. It is called LINQPad and can be found here: http://www.linqpad.net/

It is free and is capable of many things. It will browse and connect to SQL Servers and generate an appropriate data context automatically. You can then type in LINQ query expressions and run them against a DB of your choice. Once the query is run, you can move to the 'SQL' tab to see the SQL that was sent to SQL Server.

As well as this, you can also use it as a test bench for any C# or VB code you like. You can just type it in and execute it. I now have LINQPad open pretty much all the time as I am coding, as it is useful for trying stuff out, even if it's not LINQ.

To get going, have a look at the tutorial video. It is only ten or twenty minutes long and tells you all you need to know to get going with this excellent tool.

It's perhaps a bit easier than opening SQL Server Profiler every time you want to check what a LINQ query is doing!