Reader Level:
Articles

Executing SQL Query Using LINQ to SQL

By Dhananjay Kumar on Jun 29, 2011
In this article, I will discuss how we can execute a SQL Query directly from LINQ.
  • 1
  • 0
  • 27828


In this article, I will discuss how we can execute a SQL Query directly from LINQ.

Explanation

To execute a SQL query there is a method in the DataContext class called ExecuteQuery:


SqlLinq1.gif

ExecuteQuery takes two input parameters:

  1. SQL Query as string
  2. Parameters used in SQL query

    SqlLinq2.gif

And it returns an IEnumerable.

Example

The following code will execute a simple select statement and it will return IEnumerable<Person>:

SqlLinq3.gif

If you want to pass a parameter in the query, you can pass that as the second parameter.


SqlLinq4.gif

If you want to pass an input parameter as a hardcoded value then you can very much do that as below:


SqlLinq5.gifSqlLinq5.gif

For your reference the source code is as below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Linq; 
 
namespace Relatedtable
{
    class Program
    {
 
        static DataClasses1DataContext context;
        static void Main(string[] args)
        {      
            context = new DataClasses1DataContext();
 
            var result = context.ExecuteQuery<Person>("select * from Person");
            foreach (var r in result)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }
 
            int idToPass = 1;
            var result1 = context.ExecuteQuery<Person>
                          ("select * from Person where PersonID={0}", idToPass);
            foreach (var r in result1)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }
 
            Console.ReadKey(true);
 
            var result2 = context.ExecuteQuery<Person>
                          ("select * from Person where PersonID='1'");
            foreach (var r in result2)
            {
                Console.WriteLine(r.PersonID + r.FirstName);
            }
 
            Console.ReadKey(true); 
}
}
}

On pressing F5 you should get output as below:

SqlLinq6.gif

I hope this article was useful. Thanks for reading.
 

Dhananjay Kumar

Dhananjay kumar is an independent trainer, a consultant and a programmer from India. He has mentored more than 1000 professionals and known for his unique way of teaching. He has been part of the multiple projects for bi... Read more

Personal Blog: http://debugmode.net/
COMMENT USING

Trending up