Errors under load

[Maarten88]

We experience strange errors in SqlFu in a web api service that is under substantial load. Some of our most simple queries, generated from lambda expressions, generate syntax errors in SQL Server when called under load. They work by themselves, but calling them quickly 50 times will give the error

Incorrect syntax near the keyword 'from'.

The query looks like this:

var pool = dbc.Get<Models.Pool>(p => p.Id == Id);

The stacktrace:

"message":"An error has occurred.","exceptionMessage":"Incorrect syntax near the keyword 'from'.","exceptionType":"System.Data.SqlClient.SqlException","stackTrace":"   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at SqlFu.SqlFuDao.Fetch[T](DbCommand cmd, Func`2 mapper, Boolean firstRowOnly) in e:\\Projects.Net\\SqlFu\\src\\SqlFu\\SqlFuDao.cs:line 644\r\n   at SqlFu.SqlFuDao.QuerySingle[T](DbConnection cnx, String sql, Object[] args) in e:\\Projects.Net\\SqlFu\\src\\SqlFu\\SqlFuDao.cs:line 137\r\n   at Pool.Api.Domain.Queries.GetPoolById.Execute(DbConnection dbc)\r\n

[sapiens]

Can you show me generated sql? Also are you sure you're disposing the connection every time?

[Maarten88]

That is not so easy. This happens on a production server and we are rolling out a fix that does the same using QuerySingle(sql, params). Database is SQL Azure btw

[sapiens]

SqlFu doesn't support Azure, I mean it generates T-SQl but it doesn't take Azure into consideration. Anyway, I'm interested if your fix is working i.e you're skipping the expression generated sql right?

[Maarten88]

We got the generated sql:

select [Name],[PoolOwnerId],[Announcement],[PoolType],[Id], from [Pool] where ([Id] = @0)

It has a comma to much in it.

And the poco:

[Table("Pool", PrimaryKey = "Id", AutoGenerated = true, CreationOptions = IfTableExists.Ignore)]
[Index("Name", Name = "ix_PoolName", IsUnique = true)]
public class Pool : Entity<int>
{
    [Required(ErrorMessage = "Dit is een verplicht veld")]
    [ColumnOptions(IsNullable = false, Size = "50")]
    public string Name { get; set; }

    [ForeignKey("Player", "Id", OnDelete = ForeignKeyRelationCascade.NoAction)]
    public int PoolOwnerId { get; set; }

    [QueryOnly]
    [ColumnOptions(Ignore = true)]
    public List<Player> Players { get; set; }

    [QueryOnly]
    [ColumnOptions(Ignore = true)]
    public int PlayerCount { get; set; }

    [ColumnOptions(IsNullable=true)]
    public string Announcement { get; set; }

    [ColumnOptions(DefaultValue = "0")]
    public PoolType PoolType { get; set; }
}

public abstract class Entity<TKey> : Entity
{
    public Entity(TKey id)
    {
        this.Id = id;
    }

    public Entity()
    {
    }

    public TKey Id { get; set; }
}

[sapiens]

That's interesting. So, the sql is correctly generated when invoked once, but fails if called 50 times. I've created a test with your data and sql is generated ok. I'll do more digging