8

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?

Zach Green
  • 3,421
  • 4
  • 29
  • 32
  • 1
    Something must be missing - the current code cannot compile by virtue of `Id` not being defined on type `T`. – Jean Hominal Feb 22 '14 at 23:20
  • Yeah, I left off some code since the actual code is much more complicated. I have updated. – Zach Green Feb 23 '14 at 03:48
  • Yes, the constraint makes perfect sense. You can try this by running the GetQuery function with a class that does not contain Id field. The LINQ based function is strongly typed while the query is not (the where clause portion). On a desktop/server application you could use dynamic parameter instead of generic but just like with the query you will risk running into a runtime exception. – SKall Feb 23 '14 at 13:52
  • One more thing about the query is that if you want to make it generic you can't use the hardcoded "FROM Event", you would have to replace that with the value of the TableAttribute ([Table("Event")]). – SKall Feb 23 '14 at 13:55
  • 2
    What do you mean by "Adding a class constraint?" – NorCalKnockOut Feb 01 '18 at 22:48

3 Answers3

13

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.

Jean Hominal
  • 16,518
  • 5
  • 56
  • 90
  • `public T GetItem(int id) where T : BL.Contracts.IBusinessEntity, new(){ lock (locker) { return database.Table().FirstOrDefault(x => x.ID == id); } }` I can't seem to fix mine – NorCalKnockOut Feb 01 '18 at 22:44
  • @NorCalKnockOut: Have you added a `class` constraint on `T` ? If that does not fix it, please ask another question. – Jean Hominal Feb 02 '18 at 08:16
4

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);
}
SKall
  • 5,234
  • 1
  • 16
  • 25
  • I update the code with a little more context. My data model is implementing an interface that has an Id field. The FindAsync would work in this simple case, but not the more complex cases that are also failing. – Zach Green Feb 23 '14 at 03:51
  • I was having a similar issue to original poster, and modified my `LoadByIdAsync()` to call `Find()` instead of `FirstOrDefaultAsync` and all is well. – dub stylee Mar 03 '15 at 05:13
0

For those who are wondering what class constraint on T is, following is the method signature Task<T> GetAsync<T>(int id) where T : class, IDbModel, new();

Chandra Paudel
  • 1