CODEWITHSUNDEEP
c#sqlentity-framework
SGekko
SGekko

Reputation: 345

How to loop over rows using raw sql queries in EF 6?

How to get the values and loop over them from the below query? The query is returning a count.

var quantities = db.Database.SqlQuery<List<string>>
        (@"SELECT  Quantity
        FROM   Runlist WHERE(UserId = @userid) 
        GROUP BY Quantity", new SqlParameter("@userid", user.Id)).ToList();

foreach (var qty in quantities)
{

}

This is the results of the query.

Quantity
---------
1250
1750
2500
5000
5250
6250
11500
12250
12500
15500
17000
164250

enter image description here

Upvotes: 0

Views: 671

Answers (2)

Mark Rabjohn
Mark Rabjohn

Reputation: 1713

The query call looks wrong to me. The method, Database.SqlQuery<T> will return an IEnumerable<T> - so in your case it would receive IEnumerable<List<string>>, your final ToList() would project as List<List<string>>.

When you foreach over List<List<string>> each item would be List<string>, I can only imagine that the value you are getting is some oddity when EF6 manifests your string value as a list (maybe it's a character number).

The answer would probably be to do Database.SqlQuery<string> instead. As somebody else said, remove GROUP BY Quantity from your SQL if you don't want distinct quantities.

var quantities = db.Database.SqlQuery<string>
    (@"SELECT  Quantity
    FROM   Runlist WHERE(UserId = @userid) 
    GROUP BY Quantity", new SqlParameter("@userid", user.Id)).ToList();

foreach (var qty in quantities)
{
    Console.WriteLine("Quantity = " + qty);
}

Upvotes: 1

awais
awais

Reputation: 515

Replace your query like this to get a count of Quantity

List<string> quantities = new List<string>();
quantities = db.Database.SqlQuery<List<string>>
        (@"SELECT Quantity 
        FROM   Runlist WHERE(UserId = @userid) 
        GROUP BY Quantity", new SqlParameter("@userid", user.Id)).ToList();

foreach (var qty in quantities)
{
 // get value here

}

Upvotes: 1

Related Questions