Possible to return linked object with Stored Procedure

Question

I have two tables, Customers and Orders. 1 customer can have multiple orders. There is a link between the two tables.

Currently using Linq to SQL to get the data and display it. The database has grown and results were displaying pretty slow.

After some reading I added a Stored Procedure (Added the SP to the dbml file which returns ISingleResult so created a new method where i iterated through the SP data and added it to my Customers object list). I did this to speed up in obtaining and displaying the data. So far this worked well.

The problem is when I used to get order details by a customer, I used to write this

foreach (Customer c in Customer)
{

 foreach (Order o in c.Orders)
  {
    // other logic here
  }

}

Using the Stored Procedure method c.Orders was null so I added the relevant relation to the SP but Orders is always null - the only thing that has changed is the number of rows returned which is the same as the number of orders the customer has placed, however I cant iterate through the orders like I did prior to adding the SP?

Any thoughts on where ive gone wrong or what to do?

My Stored Procedure:

Select Customers.*, Orders.ID 
FROM            
Customers INNER JOIN Orders ON Customers.Ordersid = Orders.Ordersid

In code I have

public List<Customer> GetCustomers
{
    ISingleResult<StoredProcedureName> AllCustomers = myDC.StoreProcedureName;
    List<Customer> Customers = new List<Customer>();

    foreach (var c in AllCustomers)
    {
       //Add each property from SP into List properties and return List
    }

//....
}

I assume that you have a virtual property `Orders` on your customer class? And you are joining on the Orders table in the stored procedure? The procedure won't do that by itself just because of the relational table. — dckuehn, Jul 28 '16 at 16:17
Perhaps you could share the procedure and how you are calling it. — dckuehn, Jul 28 '16 at 16:20
I've added the basic SQL and C# code to my original post. Thx — Computer, Jul 29 '16 at 09:38
Can you include the mapping files and the LINQ query that you say was working? — dckuehn, Aug 01 '16 at 15:02
By that all i meant was, i dragged a table to my dbml file which did everything else for me. I didnt have to write any code on this part but all i did was get the customers and iterated through the data (code above) which was added to my Customer object and returned as a list — Computer, Aug 01 '16 at 16:02

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

I suppose that you need to use the Include clause to your query to force the dbcontext to load the navigation property "Orders" for each Customer. See this post:

How do you construct a LINQ to Entities query to load child objects directly, instead of calling a Reference property or Load()

edited May 23 '17 at 12:14

Community

1
1

answered Jul 28 '16 at 16:37

Igor Damiani

1,682
6
12

I don't think LINQ to Entities was the problem. It sounds like @Computer has LINQ working, but converting it to the stored procedure was not. – dckuehn Jul 28 '16 at 16:53

score 0 · Answer 2 · answered Jul 29 '16 at 15:04

0

You're not actually selecting the Orders object, you're only grabbing the ID.

Try simplifying your Stored Procedure to:

Select * -- take everything
FROM            
Customers INNER JOIN Orders ON Customers.Ordersid = Orders.Ordersid

answered Jul 29 '16 at 15:04

dckuehn

2,155
3
24
34

I did that and got the same result. My guess is that the GetCustomers also needs modifying so the list knows theres orders available for that customer? If so i was lost at this stage :-( – Computer Aug 01 '16 at 13:51

Possible to return linked object with Stored Procedure

2 Answers2