Sql – the correct way of reading single line of data by using Linq to SQL

linqlinq-to-sql

I'm very new to Linq, I can find multi-line data reading examples everywhere (by using foreach()), but what is the correct way of reading a single line of data? Like a classic Product Detail page.

Below is what I tried:

var q = from c in db.Products
    where c.ProductId == ProductId
    select new { c.ProductName, c.ProductDescription, c.ProductPrice, c.ProductDate };

string strProductName = q.First().ProductName.ToString();
string strProductDescription = q.First().ProductDescription.ToString();
string strProductPrice = q.First().ProductPrice.ToString();
string strProductDate = q.First().ProductDate.ToString();

The code looks good to me, but when I see the actual SQL expressions generated by using SQL Profiler, it makes me scared! The program executed four Sql expressions and they are exactly the same!

Because I'm reading four columns from a single line. I think I must did something wrong, so I was wondering what is the right way of doing this?

Thanks!

Best Answer

Using the First() extension method would throw the System.InvalidOperationException when no element in a sequence satisfies a specified condition.

If you use the FirstOrDefault() extension method, you can test against the returned object to see if it's null or not.

FirstOrDefault returns the first element of a sequence, or a default value if the sequence contains no elements; in this case the default value of a Product should be null. Attempting to access the properties on this null object will throw ArgumentNullException

var q = (from c in db.Products
    where c.ProductId == ProductId
    select new { c.ProductName, c.ProductDescription, c.ProductPrice, c.ProductDate }).FirstOrDefault();

if (q != null)
{
    string strProductName = q.ProductName;
    string strProductDescription = q.ProductDescription;
    string strProductPrice = q.ProductPrice;
    string strProductDate = q.ProductDate;
}

Also, you shouldn't have to cast each Property ToString() if you're object model is setup correctly. ProductName, ProductDescription, etc.. should already be a string.

The reason you're getting 4 separate sql queries, is because each time you call q.First().<PropertyHere> linq is generating a new Query.

Related Topic