locked
Return single row in an EF-generated class with its children set ordered in LinqToSql (c#) RRS feed

  • Question

  • I would like to make a simple operation with LinqToSql but I cannot find the right way. I have the following tables, hereby represented with an EF-generated class diagram:

    EF-generated class diagram

    Where the foreign key relationship is on SessionId (primary key table WebinarSession).

    I want to make a query that returns 1 WebinarSession with its corresponding children on WebinarTopic, ORDERED by TopicStartTime.

    Taking into account that _webinarRecordingsDB is the repository object, I already tried the solutions that looked more logic to me:

    WebinarSession lsession = _webinarRecordingsDB.WebinarTopics
       
    .OrderBy(m => m.TopicStartTime)
       
    .Select(m => m.WebinarSession)
       
    .Single(m => m.SessionId == sessionId);

    WebinarSession lsession = _webinarRecordingsDB.WebinarSessions
       
    .Single(m => m.SessionId == sessionId).WebinarTopics
       
    .OrderBy(m => m.TopicStartTime)
       
    .Single(m => m.WebinarSession.SessionId == sessionId);

    Those launch an exception because they find more rows in WebinarSession. As last (illogical) resort I also tried:

    WebinarSession lsession = _webinarRecordingsDB.WebinarSessions
       
    .Single(m => m.SessionId == sessionId);

    lsession
    .WebinarTopics.OrderBy(m => m.TopicStartTime);

    that does not launch any exception but does not perfor the sorting on lsession. Anybody might help me please? Thanks

    IMPORTANT

    I want to keep the result in a WebinarSession object

    Friday, June 17, 2011 11:06 AM

Answers

  • Hi Trinakriae,

     

    I have tried a similar query on the Northwind DB.

    I created a query which gives back a Customer who has placed the first order. (I tried to find out something similar as your issue "I want to make a query that returns 1 WebinarSession with its corresponding children on WebinarTopic, ORDERED by TopicStartTime.")

    I have created two versions:

     

     var CustomersOrderedByOrderdate = from c in db.Customers
       join o in db.Orders on c.CustomerID equals o.CustomerID
       orderby o.OrderDate
       select c;
    
     var firstcust = CustomersOrderedByOrderdate.First();
    

     


    Here I have joined the Orders table and ordered by the OrderDate of that table. The generated SQL code looks like this:

     

    SELECT TOP (1) [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    ORDER BY [t1].[OrderDate]
    

     

     

    In the second version I used the Linq methods, like you:

     

    var CustomerWithFirstOrder = db.Orders.OrderBy(ord => ord.OrderDate).First().Customer;
    

     


    This one generates this SQL query:

     

    SELECT TOP (1) [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
    FROM [dbo].[Orders] AS [t0]
    ORDER BY [t0].[OrderDate]
    
    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    WHERE [t0].[CustomerID] = @p0
    

     


    (Which is the same, just in two steps)

     

    In your case I think the following code should work fine:

    return _webinarRecordingsDB.WebinarTopics.OrderBy(m => m.TopicStartTime).First().WebinarSession;

    So we select all of the Topics, order them by TopicStartTime, get the first and query the related WebinarSession.

     

    Let me know if it is not working.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.


    • Proposed as answer by Crick3t Wednesday, June 29, 2011 4:06 PM
    • Marked as answer by Jackie-Sun Friday, July 1, 2011 6:09 AM
    Monday, June 20, 2011 10:07 AM

All replies

  • Hi Trinakriae,

     

    I think your solution is almost right. You just need to change the Single method to First or FirstOrDefault.

    For more information just click on the link.

     

    The Single method always throws an exception if there is more or less than one element in the list ("Returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.")



    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.

    Friday, June 17, 2011 11:37 AM
  • Dear Crick3t,

    thanks you for your answer.

    The Single method always throws an exception if there is more or less than one element in the list ("Returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.")


    The problem here is that I am sure SessionId exists (I am in testing phase so I use Ids that are stored for sure) and it is unique (primary key). I tried:

     

    return _webinarRecordingsDB.WebinarSessions.Single(m => m.SessionId == sessionId).WebinarTopics.OrderBy(m => m.TopicStartTime).Select(m => m.WebinarSession).First();
    return _webinarRecordingsDB.WebinarTopics.OrderBy(m => m.TopicStartTime).Select(m => m.WebinarSession).Where(m => m.SessionId == sessionId).First();
    

     

    Where the return parameter is of type WebinarSession


    and it does not perform the ordering. In other forums they said that LinqToSql does not perform this operation. Shall I believe that such powerful technology cannot order a set of elements (WebinarTopics) reached through a navigation property (WebinarSessions) and then return the father object (WebinarSession)?

     

    Thanks

     



    Friday, June 17, 2011 12:19 PM
  • Hi Trinakriae,

     

    I have tried a similar query on the Northwind DB.

    I created a query which gives back a Customer who has placed the first order. (I tried to find out something similar as your issue "I want to make a query that returns 1 WebinarSession with its corresponding children on WebinarTopic, ORDERED by TopicStartTime.")

    I have created two versions:

     

     var CustomersOrderedByOrderdate = from c in db.Customers
       join o in db.Orders on c.CustomerID equals o.CustomerID
       orderby o.OrderDate
       select c;
    
     var firstcust = CustomersOrderedByOrderdate.First();
    

     


    Here I have joined the Orders table and ordered by the OrderDate of that table. The generated SQL code looks like this:

     

    SELECT TOP (1) [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    ORDER BY [t1].[OrderDate]
    

     

     

    In the second version I used the Linq methods, like you:

     

    var CustomerWithFirstOrder = db.Orders.OrderBy(ord => ord.OrderDate).First().Customer;
    

     


    This one generates this SQL query:

     

    SELECT TOP (1) [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
    FROM [dbo].[Orders] AS [t0]
    ORDER BY [t0].[OrderDate]
    
    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    WHERE [t0].[CustomerID] = @p0
    

     


    (Which is the same, just in two steps)

     

    In your case I think the following code should work fine:

    return _webinarRecordingsDB.WebinarTopics.OrderBy(m => m.TopicStartTime).First().WebinarSession;

    So we select all of the Topics, order them by TopicStartTime, get the first and query the related WebinarSession.

     

    Let me know if it is not working.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.


    • Proposed as answer by Crick3t Wednesday, June 29, 2011 4:06 PM
    • Marked as answer by Jackie-Sun Friday, July 1, 2011 6:09 AM
    Monday, June 20, 2011 10:07 AM