Linq to SQL

Dhananjay Kumar , Microsoft MVP , Trainer and Consultant

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.

Dhananjay Kumar , Microsoft MVP , Trainer and Consultant

Video Fetching Tables and Columns name in LINQ to SQL


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GetTableNameandColumnName
{
    class Program
    {
        static void Main(string[] args)
        {
            DataClasses1DataContext context = new DataClasses1DataContext();

            #region All Tables Name

            var dataModel = context.Mapping.GetTables();
            foreach (var r in dataModel)
            {
                Console.WriteLine(r.TableName);
            }

            Console.ReadKey(true);

            #endregion

            #region Columns Name of Person table
            foreach (var r in dataModel)
            {
                if (r.TableName.Equals("dbo.Person", StringComparison.InvariantCultureIgnoreCase))
                {
                    foreach (var r1 in r.RowType.DataMembers)
                    {
                        Console.WriteLine(r1.MappedName);
                    }
                }
            }

            #endregion

            Console.ReadKey(true);
        }
    }
}

*********************************

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Dhananjay Kumar , Microsoft MVP , Trainer and Consultant

Video Executing SQL Query in LINQ to SQL


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExecutingSQLQuery
{
    class Program
    {
        static void Main(string[] args)
        {
            DataClasses1DataContext context = new DataClasses1DataContext();
            int a = 1;
            var result = context.ExecuteQuery<Person>("select * from Person where PersonID= {0}",a);
            foreach (var r in result)
            {
                Console.WriteLine(r.FirstName + r.LastName);
            }
            Console.ReadKey(true);
            var result1 = context.ExecuteQuery<Person>("select * from Person");
            foreach (var r in result1)
            {
                Console.WriteLine(r.FirstName + r.LastName);
            }

            Console.ReadKey(true);
        }
    }
}

********************************

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine