Dapper ORM and SQLite perform query

dapper mapping orm sqlite

Question

I've got a simple table in my SQLite database:

CREATE TABLE ProductCategories
(
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name NVARCHAR(128) NOT NULL UNIQUE,
    ImageUrl NVARCHAR(256),
    ParentCategoryId INTEGER,
    FOREIGN KEY (ParentCategoryId) REFERENCES ProductCategories(Id) ON UPDATE CASCADE ON DELETE CASCADE
);

I use the free version of Devart's SQLite ADO.NET provider. It works fine and I think about using Dapper ORM but I got a little problem with the type mapping. For the ProductCategories table I have the class:

public class ProductCategory
{
    public Int32 Id { get; set; }
    public String Name { get; set; }
    public String ImageUrl { get; set; }
    public Int32? ParentCategoryId { get; set; }
}

So if I try something like this:

var categories = connection.Query<ProductCategory>("SELECT * FROM ProductCategories");
foreach (var c in categories)
{
    Console.WriteLine(c.Id + " " + c.Name + " " + c.ImageUrl + " " + c.ParentCategoryId);
}

I get an execption because the ParentCategoryId field from the table can't be casted to Int32? ParentCategoryId. Also if I use the ADO.NET ExecuteReader I can always check the field for nullable and it's null if the foreign key isn't set. So I'm looking for a proper data mapping for this table.

Accepted Answer

I found the solution. I've just changed the ParentCategoryId type to INT because INTEGER is 64 bit so now there's no cast problem.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why