vendredi 8 mai 2015

Incorrect syntaxt error when using 'IN @Ids'

I'm using Dapper, and trying to get a collection of Players using the following query:

public ICollection<Player> GetPlayersWithPointsInMatch(int id)
{
    const string sql =
                    @"SELECT
                        p.Id, 
                        p.FirstName, 
                        p.LastName, 
                        pmr.Goals, 
                        pmr.Assists 
                    FROM Players p 
                    LEFT JOIN PlayerMatchStats pmr ON p.Id = pmr.PlayerId 
                    WHERE pmr.MatchId IN @Ids
                    AND (pmr.Goals > 0 OR pmr.Assists > 0)";
    return Get<Player>(sql, new[]{id});
}

The Get method looks like this:

public ICollection<T> Get<T>(string sql, ICollection<int> ids)
{
    using (var connection = new SqlConnection(ConnectionString()))
    {
        return connection.Query<T>(sql, new { ids }).ToICollection();
    }
}

And, ToICollection looks like this:

public static ICollection<T> ToICollection<T>(this IEnumerable<T> iEnumerable)
        {
            var icollection = iEnumerable as ICollection<T>;
            return icollection ?? iEnumerable.ToArray();
        }

This is the error I'm getting on connection.Query:

Additional information: Incorrect syntax near '@Ids'.

If I run this query in SQL Management Studio, it works:

SELECT
    p.Id, 
    p.FirstName, 
    p.LastName, 
    pmr.Goals, 
    pmr.Assists 
FROM Players p 
LEFT JOIN PlayerMatchStats pmr ON p.Id = pmr.PlayerId 
WHERE pmr.MatchId IN (13)
AND (pmr.Goals > 0 OR pmr.Assists > 0)

I can't really figure out where my error is, as per my understanding the query generated by Dapper should be the same as the one I write myself in SQLMS?

Aucun commentaire:

Enregistrer un commentaire