SQLite.NET - System.NotSupportedException: Cannot compile: Parameter

Question

I am using SQLite.NET Async (http://www.nuget.org/packages/SQLite.Net.Async-PCL/) in a Xamarin iOS project, but I am having a problem using the table predicate queries.

Anytime I use the Get method below, which is very simple, I receive an exception that the expression could not be compiled, System.NotSupportedException: Cannot compile: Parameter.

However, if I use a low level query, as with the GetQuery method, it works fine. Is there something I am doing wrong in the definition of my table or in the method that is preventing sqlite.net from compiling the expression?

public interface IDataModel
{
    [PrimaryKey, AutoIncrement]
    int Id { get; set; }
}

public class BaseDataModel : IDataModel
{
    [PrimaryKey]
    public virtual int Id { get; set; }
}

[Table("Event")]
public class EventDataModel : BaseDataModel
{
    public string Name { get; set; }
    public int OrganizationId { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public bool Active { get; set; }
}

public class DataService<T> : IDataService<T> where T : IDataModel, new()
{

    public virtual async Task<T> Get(int id)
    {
        var connection = await GetConnection();

        return await connection.Table<T>()
                .Where(item => item.Id == id)
                .FirstOrDefaultAsync();
    }

    public virtual async Task<T> GetQuery(int id)
    {
        var connection = await GetConnection();

        return (await connection.QueryAsync<T>("SELECT * FROM Event WHERE Id = ?", id))
             .FirstOrDefault();
    }
}

Edit #1: The problem seems to be related to the fact that my methods are generic. If I change them to specific for the model "connection.Table<EventDataModel>.Where(..." it works. Will generic methods not work?

Edit #2: I added a 'class' constraint onto T to go along with the existing 'IDataModel, new()' constraints, and that seems to have fixed the issue...Does that make sense?

Answer 1

It does make sense that adding a class constraint would solve the issue.

When you write:

public virtual async Task<T> Get(int id)
    where T : IDataModel, new()
{
    var connection = await GetConnection();

    return await connection.Table<T>()
            .Where(item => item.Id == id)
            .FirstOrDefaultAsync();
}

You do not see it, but the compiler will insert a cast between item and item.Id.

That is, what the compiler actually writes is:

public virtual async Task<T> Get(int id)
    where T : IDataModel, new()
{
    var connection = await GetConnection();

    return await connection.Table<T>()
            .Where(item => ((IDataModel)item).Id == id)
            .FirstOrDefaultAsync();
}

That cast is inserted because it would be necessary if T is a value type.

It is easy to imagine that the query provider for SQLite.net does not handle that inserted cast correctly, as doing so is not trivial.

Adding the class constraint allows the compiler to avoid inserting that cast, resulting in a simpler expression that the SQLite.net query provider apparently can translate correctly.

Answer 2

The problem, I assume, would be that the compiler doesn't know the item has property Id.

return await connection.Table<T>()
        .Where(item => **item.Id** == id)
        .FirstOrDefaultAsync();

You could create an interface with the Id field and use where T:

public interface ITable
{
    int Id { get; set; }
}

    public virtual async Task<T> Get(int id) where T : ITable
    {
       ... 

Then again you probably should just use FindAsync:

public virtual async Task<T> Get(int id)
{
    var connection = await GetConnection();

    return await connection.FindAsync<T>(id);
}