Parametrized SQLQuery

Question

I've used LINQ in my current project, but this query seemed to complicated to desing in LINQ, so I decided to try SQLQuery.

SqlConnection connection = new SqlConnection( db.Database.Connection.ConnectionString );
connection.Open();

string query = "SELECT dateadd(WEEK, x.WeekOffset,0) as [Week],"
       + " AVG(x.SessionAmount * x.SessionTime) as [WeeklyAverage],"
       + " STDEV(x.SessionAmount * x.SessionTime) as [WeeklyStDev],"
       + " FROM"
       + " ( SELECT datediff(WEEK,0, Session.Date) as WeekOffset, Session.SessionAmount, Session.SessionTime"
       + " FROM Session WHERE UserId = @userId "
       + " AND Session.Date >= @startDate AND Session.Date <= @endDate"
       + " ) x"
       + " GROUP BY WeekOffset"
       + " ORDER BY WeekOffset";

SqlParameter start = new SqlParameter("startDate",System.Data.SqlDbType.DateTime);
SqlParameter end = new SqlParameter("endDate", System.Data.SqlDbType.DateTime);
SqlParameter uid = new SqlParameter("userId", System.Data.SqlDbType.VarChar);


List<SqlParameter> paramColl = new List<SqlParameter>();
paramColl.Add(start);
paramColl.Add(end);
paramColl.Add(uid);

List<TempStdDev> Calc = new List<TempStdDev>();
Calc = db.Database.SqlQuery<TempStdDev>(query, paramColl).ToList();

I have tried several ways to implement parameters, but I keep getting different error messages, like : >"No mapping exists from object type System.Collections.Generic.List`1...to a known managed provider native type."

What is the most common/standard way to support the parameters to the query?

Scott Chamberlain · Accepted Answer · 2015-09-04 21:08:29Z

Because the signature of SqlQuery is

public DbRawSqlQuery<TElement> SqlQuery<TElement>(
    string sql,
    params object[] parameters
)

Your code is getting converted in to the equivalent to

Calc = db.Database.SqlQuery<TempStdDev>(query, new object[] { paramColl} ).ToList();

Change your code to

Calc = db.Database.SqlQuery<TempStdDev>(query, paramColl.ToArray<object>()).ToList();

so the function uses the array itself instead of putting the list in the first slot of the array.

However, because the function is params you don't need to use the paramColl list at all, just pass the members themselves.

Calc = db.Database.SqlQuery<TempStdDev>(query, start, end, uid).ToList();

P.S.

List<TempStdDev> Calc = new List<TempStdDev>();

should just be

List<TempStdDev> Calc;

You create a new list then immediately overwrite it, there is no reason for the first step.

Collectives™ on Stack Overflow

Parametrized SQLQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related