How to populate an entity model using EF raw SQL queries and/or LinQ?

Question

I want to populate the entity model below:

public class MyModel
{
    public Abc Abc { get; set; }
    public Def Def { get; set; }        
    public List<Ghi> Ghi { get; set; }
}

public class Abc
{
    [Key]
    public int ID { get; set; }
    public string SomeString { get; set; }
}

public class Def
{
    [Key]
    public int ID { get; set; } 
    public string OtherString { get; set; }
}

public class Ghi
{
    [Key]
    public int ID { get; set; }
    public int DefID { get; set; }
    public string ThirdString { get; set; }
}

With data using EF & some raw SQL queries:

using (var ctx = new ApplicationDbContext())
{
    var abc = ctx.Database.SqlQuery<Abc>(@"SELECT Abc.* FROM XY INNER JOIN Abc ON XY.AbcID = Abc.ID").ToList();
    var def = ctx.Database.SqlQuery<Def>(@"SELECT Def.* FROM XY INNER JOIN Def ON XY.DefID = Def.ID").ToList();
    var ghi = ctx.Database.SqlQuery<Ghi>(@"SELECT Ghi.* FROM XY INNER JOIN Def ON XY.DefID = Def.ID INNER JOIN Ghi ON Def.ID = Ghi.DefID").ToList();
}

But I cannot do it like this:

var myModel = new MyModel();
myModel.Abc = abc;
myModel.Def = Def;
myModel.Ghi = Ghi;

As it would throw me errors such as

Cannot implicitly convert type 'System.Collections.Generic.List' to 'MyProject.Models.Abc'

So, the questions are:

1) How can I convert a List to a Model or better directly populate a Model instead of a List using raw SQL?

2) I know LinQ can make things easier with less code to write... how can I do it with LinQ?

score 1 · Accepted Answer · answered Jun 14 '17 at 12:38

1

Your error is self explainary you should write it like this:

var myModel = new MyModel();
myModel.Abc = abc.FirstOrDefault();
myModel.Def = Def.FirstOrDefault();
myModel.Ghi = Ghi;

You trying to put collection that your get with .ToList() extenstion method to property that define as single model.

answered Jun 14 '17 at 12:38

teo van kot

12,350
10
38
70

Thank you, Teo! Damn, it was that easy. What about my second question? – Tomo Jun 14 '17 at 12:55
@Tomo actually if you using EF you should be able to map your DB structure to EF entities and then use them instead of plain SQL queries. Check [this article](https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/database-first-development/creating-the-web-application) it shows how to generate model from existing db. – teo van kot Jun 14 '17 at 12:59
I don't want that, I prefer to write it manually without the unnecessary stuff that comes with the Entity Data Model Wizard. I do the inserts without raw sql, but getting data from db I use raw sql, but I should use LinQ instead. – Tomo Jun 14 '17 at 15:26
You can use linq to sql with this stuff, otherwise you always can use linq to objects with your models after gettin your data from db – teo van kot Jun 14 '17 at 15:48

score 0 · Answer 2 · answered Jun 14 '17 at 13:41

try code:

var myModel = new MyModel();
using (var ctx = new ApplicationDbContext())
{
  myModel.ABC= (from p in ctx.XYZ
               join q in ctx.Abc on p.AbcId equals q.Id
               select q).FirstOrDefault();
 var result= (from p in ctx.XYZ
               join q in ctx.Def on p.DefId equals q.Id
               select q).ToList();
 myModel.DEF=result.FirstOrDefault();
 myModel.GHI=( from p in result
                join q in ctx.Ghi on p.Id equals q.DefId
                select q).ToList();

}

How to populate an entity model using EF raw SQL queries and/or LinQ?

2 Answers2