InvalidCastException when retrieving a record from LiteDB

Question

I inserted a record via LiteDb.Shell.exe and when I try to retrieve the record in my code, it throws an InvalidCastException. If I try to find the record via the shell, I get an Unable to cast object of type 'System.Int32' to type 'System.String'.

Here's my model

public class Query
{
    public int Id { get; set; }
    public string FilePath { get; set; }
    public string Hash { get; set; }
    public string[] Arguments { get; set; }
    public int[] PrintoutIds { get; set; }
}

Here's the shell command I used to insert a record

db.queries.insert {FilePath: "C:/Temp/Reporting Test.sql", Hash: "8074458BDE071C6B05A6EE1C718EC29CC92C89A2967D6E314EADEB0BA60F270E", Arguments: ["Tenant_ID"], PrintoutIds: [1]}

Here's the shell commands I used to try to get the record back

> db.queries.find
[1]: {"_id":{"$oid":"5a0ef5219996a32e14886d6b"},"FilePath":"C:/Temp/Reporting Test.sql","Hash":"8074458BDE071C6B05A6EE1C718EC29CC92C89A2967D6E314EADEB0BA60F270E","Arguments":["Tenant_ID"],"PrintoutIds":[1]}
> db.queries.find PrintoutIds contains 1
Unable to cast object of type 'System.Int32' to type 'System.String'.
> db.queries.find PrintoutIds contains "1"
> db.queries.find $.PrintoutIds[*] contains 1
Unable to cast object of type 'System.Int32' to type 'System.String'.
> db.queries.find $.PrintoutIds[*] contains "1"

Here's the code I'm using to retrieve the record

public IEnumerable<dynamic> DoStuffWithAQuery(int id)
{
    using (var reportDatabase = new LiteDatabase("C:/Stuff/Database/Reporting/Reports.db"))
    {
        var queryCollection = reportDatabase.GetCollection<Query>("queries");                                   
        queryCollection.EnsureIndex(x => x.PrintoutIds, "$.PrintoutIds[*]");
        Query query;
        try
        {
            // The next line throws an InvalidCastException.
            query = queryCollection.Find(x => x.PrintoutIds.Contains(id)).Single();
        }
        catch (InvalidOperationException)
        {
            throw new WebFaultException<string>("Only one query can be mapped to a printout",
                HttpStatusCode.BadRequest);
        }

        return DoSomeStuff(query);
    }
}

Here's the full stacktrace I get

The server encountered an error processing the request. The exception message is 'Specified cast is not valid.'. See server logs for more details. The exception stack trace is:

at
 lambda_method(Closure , Object , Object ) at
 LiteDB.BsonMapper.DeserializeObject(Type type, Object obj, BsonDocument value) at
 LiteDB.BsonMapper.Deserialize(Type type, BsonValue value) at
 LiteDB.BsonMapper.ToObject(Type type, BsonDocument doc) at
 LiteDB.BsonMapper.ToObject[T](BsonDocument doc) at
 LiteDB.LiteCollection`1.<Find>d__17.MoveNext() at
 System.Linq.Enumerable.Single[TSource](IEnumerable`1 source) at
 ReportService.DoStuffWithAQuery(String tenantName, Int32 id, Object parameters) in C:\C# Workspace\MyProject\ReportService.cs:line 35 at
 SyncInvokeDoStuffWithAQuery(Object , Object[] , Object[] ) at
 System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at
 System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc) at
 System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Answer 1

There are 2 thinks here

First, if your strong class use _id as an integer, you can't use shell with ObjectId (deafault). Try:

db.queries.insert { ... } id:int

Now, your document will be insert with auto-id as Int32 (_id: 1). This will avoid your mapper error here:

query = queryCollection.Find(x => x.PrintoutIds.Contains(id)).Single();

Second, it about "contains" keyword. In shell, "contains" works as String.Contains. Shell use Query.Contains(string field, string text). So, you can't pass an Int as "text".

Your query, in shell, must use =

With no index

db.queries.find $.PrintoutIds[*] = 1

Or, create an index

db.queries.ensureIndex PrintoutIds using $.PrintoutIds[*]

Now, using indexName

db.queries.find PrintoutIds = 1

But..... when you are writing Linq expressions, Contains can be from String or from IEnumerable. If are from String, works as normal Query.Contains, but if are from an IEnumerable, works with Query.EQ.

// QueryVisitor.cs : 152

// Contains (String): x.Name.Contains("auricio")
else if (method == "Contains" && type == typeof(string))
{
    var value = this.VisitValue(met.Arguments[0], null);

    return Query.Contains(this.GetField(met.Object, prefix), value);
}
// Contains (Enumerable): x.ListNumber.Contains(2)
else if (method == "Contains" && type == typeof(Enumerable))
{
    var field = this.GetField(met.Arguments[0], prefix);
    var value = this.VisitValue(met.Arguments[1], null);

    return Query.EQ(field, value);
}

Answer 2

change Id in your class Query to Query_Id and will work

public int **Query_Id** { get; set; }