SQL View Script for Entity Framework related entities

Question

I'm working on creating a SQL view and ultimately, I would like to add the view to my Entity Framework model. I understand that this can simply be done in Entity Framework but I am just experimenting with views and need to work on my SQL skills.

Let's imagine a scenario where I am pulling data from a table called Sates. State has a 0->n association to another table called City. City has a 0-n association to another table called Streets.

So my script would be something similar to:

SELECT st.Name, st.ID FROM Test.States s
LEFT OUTER JOIN Test.Cities c on c.StateID = s.ID
LEFT OUTER JOIN Test.Streets str on str.CityID = c.ID

What do I need to add to the View script to grab all the corresponding Cities and Streets in the hierarchy shown below? I basically want my View entity to have the Cities and Streets collections populated:

public partial class StateView
{
    public string Name { get; set; }
    public long ID { get; set; }
    public ICollection<City> Cities { get; set; }
}

public partial class City
{
    public string Name { get; set; }
    public long ID { get; set; }
    public ICollection<Street> Streets { get; set; }
}

P.S. my goal here is to make one trip to the DB, Thank you in advance!

score 1 · Answer 1 · answered Mar 19 '15 at 04:13

You could expand your view to simply include the additional fields in the results, then process the data in-code.

create view TestView
SELECT s.Name as sName, s.ID as sID, 
c.ID as cID, c.Name as cName, 
str.ID as strID, str.Name as strName  
FROM Test.States s
LEFT OUTER JOIN Test.Cities c on c.StateID = s.ID
LEFT OUTER JOIN Test.Streets str on str.CityID = c.ID

After added to an EF model, process as needed... e.g. for a simplified c# DataContext/LINQ setup:

YourDataContext dc = new YourDataContext();

//Single call to DB here
var newObj =
(from p in dc.TestView
group p by new { p.sID, p.sName } into g
select new StateView
{
    ID = g.sID,
    Name = g.sName,
    Cities = from pp in g.cID 
             group pp by new { pp.cID, pp.cName } into gg
             select new City
             {
                  ID = gg.cID
                  Name = gg.cName,
                  Streets = gg.Streets.ToList()

             }
}).toList();

Note this code is untested, and may require tweaking.

The idea is to include all of the data in your initial view, then process the view into your objects using grouping in LINQ. Without having a Street structure, I just toList()'ed it (would need to be modified for Street structure instead). The resulting object should be a List with all of the child properties set as well.

the ToList() on Streets would be an extra call to the DB for each city. This assumes eager loading where in some cases, you want lazy loading — Amir, Mar 23 '15 at 19:05

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

My solution was to add manual associations through the edmx model.

Please refer to: Entity Framework - Add Navigation Property Manually for additional info.

edited May 23 '17 at 11:43

Community

1
1

answered Mar 23 '15 at 19:05

Amir

486
4
14

SQL View Script for Entity Framework related entities

2 Answers2