Building a Raw SQL String from Linq Query Results?

Question

I need to make a selection from a SQL Server table and concatenate the results into a SQL string so I end up with var sqlString = "Select blah from myTable where blah blah order by blah":

enter image description here

Here's my LINQ. I'm trying to select out the columns in the order I need them & concatanate them into 1 string:

  var query = (from a in _Context.tbArticleTypeAssets
                      where a.ArticleType == ArticleType
                      select new {sqlQuery = a.ColSelection + "" + a.FromTable + "" + a.whereclause + "" + a.orderBY});

         string queryResult = query.ToString();

The result is a Linq output which I don't want. I just want the values of the strings. Is there a way to achieve this?

Ehsan Sajjad · Accepted Answer · 2015-03-16 14:14:54Z

2

linq query will return a collection, you have to use First() or FirstOrDefault() to get the item :

 string queryResult = query.FirstOrDefault().sqlQuery;

better is to check for null as well:

string queryResult =  query.FirstOrDefault() != null ? query.FirstOrDefault().sqlQuery : string.Empty;

edited Mar 16, 2015 at 14:14

answered Mar 16, 2015 at 14:08

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hardgraf Over a year ago

Great, thanks. Why do I get 'sqlQuery = SELECT blah blah' in my result? On the Select new I have to give a name but I don't want the name to appear in the resulting string?

Ehsan Sajjad Over a year ago

@Hardgraf the way I posted should not give you like that, and you can skip new which is creating anonymous object actually

Ehsan Sajjad Over a year ago

I mean like : select a.ColSelection + "" + a.FromTable

Robert McKee · Accepted Answer · 2015-03-16 15:47:42Z

0

Try this:

 var query = _Context.tbArticleTypeAssets
        .Where(a=>a.ArticleType == ArticleType)
        .Select(a=>a.ColSelection + "" + a.FromTable + "" + a.whereclause + "" + a.orderBY)
        .ToList();

or if your query will always only return one record, then you can do this:

 var query = _Context.tbArticleTypeAssets
        .Where(a=>a.ArticleType == ArticleType)
        .Select(a=>a.ColSelection + "" + a.FromTable + "" + a.whereclause + "" + a.orderBY)
        .First();

answered Mar 16, 2015 at 15:47

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Collectives™ on Stack Overflow

Building a Raw SQL String from Linq Query Results?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related