Database.SqlQuery -> Entity Framework Objects

Question

I want to use Database.SqlQuery in Entity framework to run a custom JOIN operation. I don't want to use LINQ to do the JOIN because it is doing a horrible job of generating performant SQL on the backend and I just want to control what it does.

So my question is -- How can I get a set of objects (it's a JOIN from table A to table B, and I want both an object of type A and an object of type B) back out from an INNER JOIN operation on Database.SqlQuery?

That is one of the most interesting features of Dapper: github.com/StackExchange/dapper-dot-net#multi-mapping — Zein Makki
– Zein Makki, Commented Sep 5, 2016 at 8:28
So concretely how would I use that here -- do you have an example you can share? — A X
– A X, Commented Sep 5, 2016 at 8:30

Mark Shevchenko · Accepted Answer · 2016-09-05 23:56:22Z

4

As far as I know the SqlQuery method uses property names to map columns to properties.

So you can just declare the class with properties of query and then split it to pair A and B.

Example:

public class AB
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Title { get; set; }
}

var abs = dbContext.Database.SqlQuery<AB>(@"SELECT A.Id, A.Name, B.Title
                                            FROM A JOIN B ON A.Id = B.Id");

var a_and_bs = from ab in abs
               select new
               {
                   A = new A { ab.Id, ab.Name },
                   B = new B { ab.Title }
               };

edited Sep 5, 2016 at 23:56

answered Sep 5, 2016 at 8:48

Mark Shevchenko

8,2551 gold badge28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database.SqlQuery -> Entity Framework Objects

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related