New to EF - Database First

Question

Hi all,

We are looking at an architecture re-write for an existing web application without making major changes to our existing database schema, so looking at EF 6.0 database first approach. 

Just some initial questions:

- Is it better to Join tables via EF or map directly to a SQL View

- We have hierarchies implemented in our schema - does EF work well with this?

Any tips/pointers/links appreciated!

Keith. 

Answer 1

Hello,

>> Is it better to Join tables via EF or map directly to a SQL View

Are these tables which you want to do a join operation have relationships with each other?

For example, we have Order and OrderDetails tables, they have a relationship 1:N and OrderDeatils has a OrderId field which is a foreign key. Then we don't need join and view actually, per se. What we need do is use Navigation Properties for Order and OrderDetails in order to access them without the need for joins. And in entity framework, it is lazy load by default that means it will load related entities, in this example, if we do a query as below:

var result=from order in db.Orders

                Select order;

It will load OrderDetails for us, too.

For Loading Related Entities in entity framework, please refer to link below:

http://msdn.microsoft.com/en-in/data/jj574232.aspx

For navigation relationship in entity framework, please refer to link below:
http://msdn.microsoft.com/en-in/data/jj713564.aspx

>> We have hierarchies implemented in our schema - does EF work well with this?

Yes, Entity Framework supports this relationship and for How to work with Hierarchal data in Entity Framework, please have a look at this article:

http://goldytech.wordpress.com/2011/06/27/how-to-work-with-hierarchal-data-in-entity-framework/

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi there,

I have my recursive hierarchy mapped correctly with EF.

I have an issue - I want to get all recursive children of a particular node.

I have linq returning me the child nodes using this:

 var recursiveList = db.ProcessHierarchyItems
                .Where(x => x.ProcessHierarchyItemId == id)
                .SelectMany(x => x.Children);

Is there a way of getting all the recursive child nodes to the bottom of the hierarchy?

Thanks again!

Keith.

Answer 3

Hello,

For this, this thread may be helpful to you:
http://stackoverflow.com/questions/14989119/recursive-query-using-linq

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

Hi Fred,

Can you give me more detail??? Still struggling.

Thanks,

Keith. 

Answer 5

Hello,

With LINQ query, it may be impossible, I suggest using the store procedure with the CTEin SQLServer.

For this I made a sample and please have a look at it below:

My table and data:

 

The SP:

CREATE PROCEDURE [dbo].[HierarchyProcedure]

       @param1 int = 0

AS

       WITH DirectReports (category_id,name,parent)

AS

(

-- Anchor member definition

    SELECT c.category_id, c.name,c.parent

    FROM dbo.category AS c

    WHERE  c.parent = @param1

    UNION ALL

-- Recursive member definition

    SELECT c.category_id, c.name,c.parent

    FROM dbo.category AS c

    INNER JOIN DirectReports AS d

        ON c.parent = d.category_id

)


SELECT distinct category_id,name,parent

FROM DirectReports

RETURN 0

We just need to pass the parent value and we can get all the sub nodes under this parent node like below:

using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())            {                var result = db.HierarchyProcedure(0);            }

When I pass the 0, the result is like below:

 

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 6

Hi Fred,

Thanks for the reply. I knew that I could go down the SP route, but was looking to use linq with EF standard table mapping.

For anybody looking at this, I did find a way using LINQ:

public static IEnumerable<T> Traverse<T>(this IEnumerable<T> items, 
    Func<T, IEnumerable<T>> childSelector)
{
    var stack = new Stack<T>(items);
    while(stack.Any())
    {
        var next = stack.Pop();
        yield return next;
        foreach(var child in childSelector(next))
            stack.Push(child);
    }
}

 var allDescendents = db.ProcessHierarchyItems
                .Where(x => x.ProcessHierarchyItemId == id)
                .Traverse(x => x.Children)

Performance wise, I am guessing the SP way is more efficent but nicve to have both options.

Thanks,

Keith. 

Answer 7

Hi,

>> Performance wise, I am guessing the SP way is more efficent but nicve to have both options.

It should be that the SP is more effective since the LINQ statement will be translated to T-SQL and we can see that the created T-SQL I guess it will be complex.

Anyway, you have found what you want. Cheers.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.