CODEWITHSUNDEEP
c#sqlsql-serverlinqentity-framework
Hardgraf
Hardgraf

Reputation: 2616

Linq to Entities: Parameterised Raw Sql query?

I'm writing a WCF service which utilises Entity Framework 6 (EF) to query SQL Server. Most of my query methods are written in LINQ. All good.

I need to write a parameterised query. Had a read through this: http://adicodes.com/entity-framework-raw-sql-queries-and-stored-procedure-execution/ This is what I've got so far:

       public string GetLinkItemValue(string linkTable, string linkItem, string linkGUID)
       {
         try
         {             
           var LinkItem = new SqlParameter("@linkItem", linkItem);
           var LinkTable = new SqlParameter("@linkTable", linkTable);
           var LinkGUID = new SqlParameter("@LinkGUID", linkGUID);
           var objectresult = _Context.Database.SqlQuery<string>("SELECT c.@linkItem FROM ISESEntities.@linkTable AS c WHERE c.CCCPGUID= @linkGUID");

           return objectresult.ToString();
         }
          catch (Exception)
          {
             return string.Format("There was a problem retrieving the Link Item value for LinkTable[{0}],LinkItem[{1}],LinkGUID[{2}].", linkTable, linkItem, linkGUID);
          }       
      }

This obviously isn't quite right and is corpsing on the query string at c.@linkItem, I've got it to return the query string as the objectresult string but can't seem to return the result from the query.

Infact even when I simplify the query & remove the parameters:

var objectresult = _Context.Database.SqlQuery<string>("SELECT a.ArticleTitle FROM ISESEntities.vwArticles AS a where a.ArticleId = 01");

objectResult returns the sql query string value rather than the result.

Where am I going wrong?

Upvotes: 1

Views: 2775

Answers (1)

Jamiec
Jamiec

Reputation: 136094

In a SQL string you cannot parameterize the column name(s) or the table name(s).

Your only option is to dynamically build the SQL string to include these values

var objectresult = _Context.Database.SqlQuery<string>(
    String.Format("SELECT c.{0} FROM ISESEntities.{1} AS c WHERE c.CCCPGUID= @linkGUID",linkItem,linkTable));

And then of course remove those parameters elsewhere.

As for the second issue, the documentation tells us

The query is not executed when this object is created; it is executed each time it is enumerated, for example by using foreach.

so you must enumerate it to see the results of executing the query.

foreach(var item in objectresult)
  // do something

perhaps you only want the first result (judging by the query itself) in which case this should also work (as it enumerates the result)

return objectresult.FirstOrDefault();

Upvotes: 1

Related Questions