Querying into a complex object with sqlkata

Question

I cant built a complex object with a query. How I do?

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public Contact Contact { get; set; }
}

public class Contact
{
    public long Id { get; set; }
    public string FoneNumber { get; set; }
}

score 1 · Answer 1 · answered Aug 24 '18 at 14:14

My code:

        var compiler = new SqlServerCompiler();
        var db = new QueryFactory(connection, compiler);

        var person = db.Query("Person")
                        .Select("Person.Id", "Person.Name", "Contact.Id", "Contact.FoneNumber")
                        .Join("Contact", "Person.Id", "Contact.PersonId")
                        .Where("Person.Id", 1)
                        .FirstOrDefault<Person>();

score 1 · Answer 2 · answered Aug 27 '18 at 13:13

As you have wrote before, use the Join Method to join with the "Contact" table,

var row = db.Query("Person")
            .Select(
                "Person.Id",
                "Person.Name",
                "Contact.Id as ContactId",
                "Contact.FoneNumber as FoneNumber"
            )
            .Join("Contact", "Person.Id", "Contact.PersonId")
            .Where("Person.Id", 1)
            .FirstOrDefault();

score 0 · Answer 3 · answered Aug 24 '18 at 14:01

You can use "Multi-Mapping" feature of Dapper.

    [Test]
    public void Test_Multi_Mapping()
    {
        using (var conn = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=test"))
        {
            var result = conn.Query<Person, Contact, Person>(
                "select Id = 1, Name = 'Jane Doe', Id = 2, FoneNumber = '800-123-4567'",
                (person, contact) => { person.Contact = contact;
                    return person;
                }).First();

            Assert.That(result.Contact.FoneNumber, Is.EqualTo("800-123-4567"));
        }
    }

You can also use ".QueryMultiple". Read Dapper's documentation, or take a look at the unit tests for more examples.

But I want use sqlkata, because I need use dynamic WHERE clause. — VFB, Aug 24 '18 at 14:12

score 0 · Answer 4 · answered Jan 13 '22 at 01:41

You can use a combination of SqlKata (@amd's answer) and Dapper (@Void Ray's answer):

var query = new SqlKata.Query(...);      //compose your sqlkata query as user amd indicated above
var compiler = new PostgresCompiler();   //or mssqlcompiler or whatever
var compiled = compiler.Compile(query);
var sql = compiled.Sql;
var parameters = new DynamicParameters(compiled.NamedBindings);
var result = await db.QueryAsync<Person, Contact,Person>(sql,(p,c)=>{p.Contact = c;  return p;},parameters);

Querying into a complex object with sqlkata

4 Answers4