How to view the generated SQL Query of LINQ

Have you ever thought of viewing generated SQL query of LINQ you write? You may need the generated query for the debugging and the logging purposes. In this post let us see how to print the generated SQL query on the console.

Let us consider you have written LINQ as follows:


            DataClasses1DataContext context = new DataClasses1DataContext();
            IEnumerable<Order> result = context.Orders;
            var product = result.Where(x => x.OrderID == 10248);
            context.Log = Console.Out; 
            foreach(var r in product)
            {
               
                Console.WriteLine(r.ShipName);
            }

You can print the generated SQL by putting one line of code just before data gets loaded. In this scenario put following line of code anywhere but before the foreach statement.

image

You will get the generated SQL query in console as follows: clip_image001

You can set or get log of the DataContext in a TextWriter. If required you can save them on a file system etc.

Happy Coding.

What is difference between First and FirstOrDefault in LINQ?

Last week I were presenting LINQ to group of almost 100 audience. While writing query a question raised that what is difference between First and FirstOrDefault . At that time I was not very sure about answer so I asked audience to help me. One from audience answered this question. So credit of this blog to him. For benefit of all I am documenting answer below,

Let us focus on answer of this question. I am sure that you may have come across following query in LINQ.

In below query we are fetching First record on given condition.

image

Now there are two kind of result you can expect.

  • Either a product on basis of criteria
  • Or there is no product on given criteria

If there is no product on the critera you are searching then LINQ will throw you run time exception of type InvalidOperationException

image

So when you are working with First() make sure that you have handled the InvalidOperationException else you may encounter exception if there is no result matched on given criteria. So you may want to modify code as given below,

try
 {
 Product result = (from r in GetProducts()
 where
 r.ProductId == 1
 select r).First();
 }
 catch (InvalidOperationException ex)
 {
 Console.WriteLine(ex.Message);
 }

If there is no match then you will get output as below,

clip_image002

Next option is FirstOrDefault . So you may have come across following query. If there is no Product matched on given criteria then LINQ will return NULL.

image

So assume there is not matched Product and query has returned NULL then in that case if you access result then you will get exception as following

image

To avoid run time NullReferenceException you can modify code as following,

Product result1 = (from r in GetProducts()
 where
 r.ProductId == 1
 select r).FirstOrDefault() ;
 if (result1 != null)
 {
 Console.WriteLine(result1.ProductPrice);
 }

else
 {
 Console.WriteLine("No Record Found");
 }

&nbsp;

Now when there is no Product code will gracefully print a message that No Record Found. This is basic difference between First and FirstOrDefualt. I hope you find this post useful. Thanks for reading.