How To Run SQL Statement from LINQ

Introduction

Sometimes we need to run a SQL statement from LINQ. This article will show how to do this.

Using the Code

First you create a *.dbml file for mapping your database from 'Add New Item' in Visual Studio.NET 2008. As you know, a query in LINQ is different from a query in SQL. Here I create a method called GetMember which contains a SQL statement as a string which I need to execute from LINQ, and returns a list of members which make this method as datasource to grid view later.

I create this method for searching members by any parameter entered and with each other:

GeoDataContext MemberConnection = new GeoDataContext(); //connection
public List<Member> GetMemberSearchAll(string name, string fname, string MemberNo,
    string QualificationClass, string WorkClass, string GeoID)
{
    var sql = @"SELECT DISTINCT * FROM dbo.Member where MemberCode <> 0";
    if (name != "")
    {
        sql = sql + " and (dbo.Member.MemberName like '%{0}%')";
    }
    if (fname != "")
    {
        sql = sql + "and (dbo.Member.MemberFameName like '%{1}%')";
    }
    if (MemberNo != "")
    {
        sql = sql + "and (dbo.Member.MemberNo = {2})";
    }
    if (QualificationClass != "")
    {
        sql = sql + " and (dbo.Member.QualificationClass = {3})";
    }
    if (WorkClass != "")
    {
        sql = sql + " and (dbo.Member.WorkClass = {4})";
    }
    if (GeoID != "")
    {
        sql = sql + "and (dbo.Member.GeoID ={5})";
    }
    sql = string.Format(sql, name, fname, MemberNo, 
		QualificationClass, WorkClass, GeoID);
    var quary = MemberConnection.ExecuteQuery<Member>(sql);
    return quary.ToList();
}

Here I first formulate strings by both SQL statement and parameter by order, then I execute the method called ExecuteQuery from my connection which takes a parameter as a SQL query. Its summary in documentation of Visual Studio is "Executes SQL queries directly on the database and returns objects" then pass this SQL formatted to it.

Thanks, I hope this will help somebody.

History

• 30^th March, 2009: Initial post