Executing SQL Query using LINQ to SQL

Waqas Habib June 30, 2011

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

Explanation

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

clip_image002

ExecuteQuery takes two input parameter

1. SQL Query as string

2. Parameters used in SQL query

clip_image003

And it returns an IEnumerable.

Example

Below code will execute a simple select statement and it will return IEnumerable<Person>

clip_image005

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

clip_image007

If you want pass input parameter as hardcoded value you can very much do that as below

clip_image009

For your reference source code is as below,

        01    using System;  
        02    using System.Collections.Generic;  
        03    using System.Linq;  
        04    using System.Text;  
        05    using System.IO;  
        06    using System.Data.Linq;  
        07     
        08    namespace Relatedtable  
        09    {  
        10        class Program  
        11        {  
        12     
        13            static DataClasses1DataContext context;  
        14            static void Main(string[] args)  
        15            {  
        16                context = new DataClasses1DataContext();  
        17     
        18                var result = context.ExecuteQuery<Person>("select * from Person");  
        19                foreach (var r in result)  
        20                {  
        21                    Console.WriteLine(r.PersonID + r.FirstName);  
        22                }  
        23     
        24                int idToPass = 1;  
        25                var result1 = context.ExecuteQuery<Person>  
        26                              ("select * from Person where PersonID={0}", idToPass);  
        27                foreach (var r in result1)  
        28                {  
        29                    Console.WriteLine(r.PersonID + r.FirstName);  
        30                }  
        31     
        32                Console.ReadKey(true);  
        33     
        34                var result2 = context.ExecuteQuery<Person>  
        35                              ("select * from Person where PersonID='1'");  
        36                foreach (var r in result2)  
        37                {  
        38                    Console.WriteLine(r.PersonID + r.FirstName);  
        39                }  
        40     
        41                Console.ReadKey(true);  
        42    }  
        43    }  
        44    }  