Linq2DB and query for dynamic

Question

I'm trying to perform a query without having created a model that maps it. Consider this snippet

public IDictionary<string, int> GetParentelaMapping()
    {
        using (var conn = dataContextFactory.Create())
        {
            var result = conn.Query<dynamic>("SELECT ID_GRADO_PARENTELA,GRADO_PARENTELA FROM GRADO_PARENTELA")
                .ToDictionary(
                    row => (string)row.GRADO_PARENTELA,
                    row => (int)row.ID_GRADO_PARENTELA, StringComparer.OrdinalIgnoreCase);

            return result;
        }
    }

It gaves me an exception that Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: ''int' does not contain a definition for 'GRADO_PARENTELA''

How can I handle such a case? Thanks

Such queries are not supported right now. You can create feature request. Easiest solution will be to define class with those properties and use it like this: `db.FromSql(...sql..).ToDictionary(...)` — , Feb 05 '19 at 10:39

score 1 · Accepted Answer · answered Feb 05 '19 at 13:58

Unfortunately this feature currently is not supported. Track new issue #1591 for that.

As a workaround i suggest to define additional class for reading such result:

class ParentelaMapping
{
    public int ID_GRADO_PARENTELA { get; set; }
    pulbic string GRADO_PARENTELA { get; set; }
}

public IDictionary<string, int> GetParentelaMapping()
{
    using (var conn = dataContextFactory.Create())
    {
        var result = conn.Query<ParentelaMapping>("SELECT ID_GRADO_PARENTELA,GRADO_PARENTELA FROM GRADO_PARENTELA")
            .ToDictionary(
                row => row.GRADO_PARENTELA,
                row => row.ID_GRADO_PARENTELA, StringComparer.OrdinalIgnoreCase);

        return result;
    }
}

P.S.

linq2db designed to work better with linq queries so consider to rewrite your query in the following way:

[Table("GRADO_PARENTELA")]
class GrandoParentela
{
    public int ID_GRADO_PARENTELA { get; set; }
    pulbic string GRADO_PARENTELA { get; set; }
}

public IDictionary<string, int> GetParentelaMapping()
{
    using (var conn = dataContextFactory.Create())
    {
        var result = conn.GetTable<GrandoParentela>()
            .ToDictionary(
                row => row.GRADO_PARENTELA,
                row => row.ID_GRADO_PARENTELA, StringComparer.OrdinalIgnoreCase);

        return result;
    }
}

Thanks for your reply, what do you mean that it works better with linq queries, it's faster? — advapi, Feb 05 '19 at 14:01
Both approaches are extremely fast. Generated query will be cached and reused, so next run will be just executing query. Benefits here is to keep away from raw SQL which leads in better code support later. Also you can think about using Query Decomposition and code reusing which are not so obvious when using string concatenations. — Svyatoslav Danyliv, Feb 05 '19 at 14:26

Linq2DB and query for dynamic

1 Answers1