ServiceStack ORMLite and Dapper Working together Issues

c# dapper ormlite-servicestack

Question

This is my third day doing experiments with ServiceStack ORMLite. My sample project which used to be working only with Dapper, now it is a mix of both Dapper and ServiceStack ORMLite, taking the best of both worlds.

Last night I was struggling with a Dapper query. It is basically a select joining 4 tables which was runing ok before ORMLite's code came in. Now it is failing over and over for the table mapping against objects. It doesn't break it just assigns wrong values to the objects.

I had to move on so as a temporary workaround what I did was executing each of the 4 selects without the SQL join using a Dapper's QueryMultiple and later, on C# joining the results one by one.

Please don't start laughing. I know this is of course, not the best solution at all. Yet it still makes one trip to DB but it doesn't feel natural. When the number of rows increases most likely this will be a performance issue.

This morning I think I found the source of my issue: I am droping and creating the tables using ORMLite and noticed that the field order isn't quite what I expected it to be. Before ORMLite code I just ran SQL scripts manually so I decided which order the fields had. Since my project needs to support MySql, Postgresql and SQL Server as the final user desires, it would be a pain to keep in sync three versions of such scripts. I would like to automate this process with ORMLite and take advantage out of it of course.

Now let me explain my theory and you expert guys may decide whether I am right or wrong. Dapper's query using joins splits the results of each table to map to each class, based on naming conventions. It expects the "Id" or "id" field to be the first field on a table.

I know Dapper has a "SplitOn" option to handle tables not following its naming convention ("IdProduct" as PK instead of "Id") but in my case it cannot be used like that, since I really don't have any control on how fields are ordered on ORMLite's CreateTable function. It could end up with "id" being on the middle and thus, causing the same issue I have now.

Maybe looking at my code can give you more clues. This is the code which was working fine before ORMLite:

        using (var conn = this.ConnectionString.OpenDbConnection())
        {
            string sql = @"
                select  *
                from    insumo i
                join    unidadmedida um on um.id = i.idum
                join    lineainsumo l on l.id = i.idlinea
                left outer join archivo a on a.id = i.idimagen;";

            var insumos = conn.Query<Entities.Insumo, Entities.UnidadMedida,
                Entities.LineaInsumo, Entities.Archivo, Entities.Insumo>(sql, (i, um, l, a)
                    =>
                    {
                        i.UnidadMedida = um; i.Linea = l; i.Fotografia = a ?? new Entities.Archivo();
                        return i;
                    }).ToList(); //Dapper

            return insumos;
        }

And this is the temporary workaround:

        using (var conn = this.ConnectionString.OpenDbConnection())
        {
            string sql = @"
                select * from insumo;
                select * from unidadmedida;
                select * from lineainsumo;
                select  a.*
                from    archivo a
                join    insumo i on i.idimagen = a.id;";


            var q = conn.QueryMultiple(sql); //Dapper
            var insumos = q.Read<Entities.Insumo>().ToList();
            var ums = q.Read<Entities.UnidadMedida>().ToList();
            var lineas = q.Read<Entities.LineaInsumo>().ToList();
            var archivos = q.Read<Entities.Archivo>().ToList();

            foreach (var i in insumos)
            {
                i.UnidadMedida = ums.FirstOrDefault(c => c.Id == i.IdUm);
                i.Linea = lineas.FirstOrDefault(c => c.Id == i.IdLinea);
                i.Fotografia = archivos.FirstOrDefault(c => c.Id == i.IdImagen) ?? new Entities.Archivo();
            }

            return insumos;
        }

I was looking into ORMLite's code and it seems the function ToCreateTableStatement may be the place to modify in order to solve my problem. Perhaps adding an attribute to each field on my class definition to state the desired order of field creation could work?

I was thinking on adding this to FieldDefinition code:

public bool FirstField { get; set; }

And adding an attribute to decorate C# fields like this:

[Alias("name")]
public string Name { get; set; }

[Alias("id")]
[PrimaryKey]
[FirstField(true)]
public int Id { get; set; }

Then when creating the field's the code could follow the values on such property and allow to control the final field's position on the table.

Please let me know if I am into the right track or, maybe there is another alternative more elegant that this one.

Thanks in advance.

Accepted Answer

It seems to be easier to just override the abstract Id on every class instead of modifying ORMLite's source code.

The project's schedule is narrow to try another approach. Perhaps for the next one I can find the time to try what @mithz kindly suggests.




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