none
How do I access the dependant table in code first EF using LINQ? RRS feed

  • Question

  • Hello,

    VS2013, EF6 code first, VB (MVC5)

    I'm having difficulty understanding some key concept about accessing data in a dependent table once it has been saved.  Since I'm misunderstanding something, my question might not be perfect.  I'm working with MVC5, although I don't think that is relevant to the issue I'm having.  This seems to me to be a straightforward issue regarding code first EF and LINQ.

    This is my Model:

    Public Class Order
      Public Property OrderID As Integer
      Public Property Products As New List(Of Product)
    End Class
    
    Public Class Product
      Public Property ProductID As Integer
      Public Property name As String
    End Class

    For my DbContext I simply added the last line below to the existing context.  As you can see, I only created the DbSet for Orders.  The article I mention below indicates I do not need to create a DbSet for Product, and if I do, then the table becomes independent if I understood it correctly.  Here's the context:

    Public Class ApplicationDbContext
      Inherits IdentityDbContext(Of ApplicationUser)
      Public Sub New()
        MyBase.New("DefaultConnection", throwIfV1Schema:=False)
      End Sub
    
      Public Shared Function Create() As ApplicationDbContext
        Return New ApplicationDbContext()
      End Function
    
      Public Property orders As DbSet(Of Order)
    
    End Class

    These are the resulting diagrams from the code above from edmx and SSMS (I used this link to derive the EF diagram):

    This is my Code for storing the data:

      Function About() As ActionResult
        ViewData("Message") = "display just one order as it's built"
    
        Dim thisOrder As New Order
        For i = 1 To 10
          Dim aProd As New Product
          aProd.name = "Product #" & i
          thisOrder.Products.Add(aProd)
        Next
        db.orders.Add(thisOrder)
        db.SaveChanges()
    
        Return View(thisOrder)
      End Function

    I can watch the data be created and stored, or display it as in this View code:

    @ModelType Order
    @Code
        ViewData("Title") = "About"
    End Code
    
    <h2>@ViewData("Title").</h2>
    <h3>@ViewData("Message")</h3>
    
    <p>Use this area to display the record data</p>
    
    @Model.OrderID
    <br />
    @For Each item In Model.Products
      @item.name
      @<br />
    Next

    The view code above extracts the individual products from the dependent class, Product.  The display looked like this:

    About.
    
    display just one order as it is built
    
    Use this area to display the record data
    9 
     Product #1 
     Product #2 
     Product #3 
     Product #4 
     Product #5 
     Product #6 
     Product #7 
     Product #8 
     Product #9 
     Product #10 

    And I can see the dependent table in SSMS and it's full of Products, 10 per order, and 9 different orders, for 90 rows.  I just can't get at it.  I don't understand how to create the LINQ code to do that.  The following code doesn't work and may give insight to what I lack in understanding: (I guess there's not really any LINQ in the code below, but nothing I tried worked)

      Function Contact() As ActionResult
        ViewData("Message") = "Display the whole database"
    
        Dim allOrders As New List(Of Order)
        For Each item In db.orders
          Dim anOrder As New Order
          For Each pitem In item.Products
            Dim aProd As Product
            aProd = pitem
            anOrder.Products.Add(aProd)
          Next
          allOrders.Add(anOrder)
        Next
    
        Return View(allOrders)
      End Function

    BTW, I did notice in this link that the database diagrams are slightly different than mine:

    My SSMS diagram on the right seems the same, but my edmx diagram on the left is different in the Navigation Properties for the dependent table.  They also have additional properties defined in their Models as shown above.  The article doesn't indicate code first was used, so I guess there may be some variance in how the model is designed; for example, EF code first automatically creates the key in the dependent table back to the primary table, but I'm not sure I understand the 2 virtual properties above, and based on the diagrams, it appears EF code first at least created one of the navigational properties as well, but not the other.  Do I need both those navigational properties to enable additional methods in the LINQ syntax?

    Regardless of the above, I think my problem is more basic.  Anyway, my 2 questions are:

    Q1) Could someone show me the way to access the dependent Class/Table data? 

    Q2) And while it's always difficult to understand what someone's misunderstanding might be, possibly point out where I'm not thinking correctly?

    Thanks for any help and Best Regards,

    Alan





    • Edited by Alan Wheeler Tuesday, December 9, 2014 5:12 PM hopefully final clarificatons
    Tuesday, December 9, 2014 4:50 PM

Answers

  • http://www.codeproject.com/Tips/298963/Understand-Lambda-Expressions-in-minutes

    http://www.dotnetperls.com/lambda-vbnet

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

    For you using VB, it would be this in Lambda

    dim blogs1 = context.Blogs 
                              .Include(Function(b) b.Posts) 
                              .ToList()
     

    Your next assignment would be to use the "Include" statement in a VB Linq statement without Lambda, and Bing and Google are your friends. You should also look up a Linq "Join" statement.

    Oh by the way,you can do the below.

    foreach p as Post in blog1.Posts

    dim name = p.Name

    next

    • Marked as answer by Alan Wheeler Wednesday, December 10, 2014 10:00 PM
    Wednesday, December 10, 2014 2:51 AM
  • Hello Alan,

    >>And I can see the dependent table in SSMS and it's full of Products, 10 per order, and 9 different orders, for 90 rows.  I just can't get at it.

    If this means that you want to display all orders with its Products as:

    Order1

    Product1

    Order9

    Product1

    If it is, for your first question, I think you do not even use the LINQ query, you could use the below line code:

    Dim allOrders As New List(Of Order)
    
    allOrders = db.orders.Include("Products").ToList()
    

    Using the Include method to load its dependent entities explicitly. Since you do not have use the “virtual”(syntax in C#, in VB it should be Overridable), it would not lazy load its dependent entities so that in your provided foreach query, the Product would be 0. And it is recommended to add the navigation property in both side and also the foreign key.

    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.

    • Marked as answer by Alan Wheeler Wednesday, December 10, 2014 10:00 PM
    Wednesday, December 10, 2014 3:44 AM
    Moderator

All replies

  • http://www.codeproject.com/Tips/298963/Understand-Lambda-Expressions-in-minutes

    http://www.dotnetperls.com/lambda-vbnet

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

    For you using VB, it would be this in Lambda

    dim blogs1 = context.Blogs 
                              .Include(Function(b) b.Posts) 
                              .ToList()
     

    Your next assignment would be to use the "Include" statement in a VB Linq statement without Lambda, and Bing and Google are your friends. You should also look up a Linq "Join" statement.

    Oh by the way,you can do the below.

    foreach p as Post in blog1.Posts

    dim name = p.Name

    next

    • Marked as answer by Alan Wheeler Wednesday, December 10, 2014 10:00 PM
    Wednesday, December 10, 2014 2:51 AM
  • Hello Alan,

    >>And I can see the dependent table in SSMS and it's full of Products, 10 per order, and 9 different orders, for 90 rows.  I just can't get at it.

    If this means that you want to display all orders with its Products as:

    Order1

    Product1

    Order9

    Product1

    If it is, for your first question, I think you do not even use the LINQ query, you could use the below line code:

    Dim allOrders As New List(Of Order)
    
    allOrders = db.orders.Include("Products").ToList()
    

    Using the Include method to load its dependent entities explicitly. Since you do not have use the “virtual”(syntax in C#, in VB it should be Overridable), it would not lazy load its dependent entities so that in your provided foreach query, the Product would be 0. And it is recommended to add the navigation property in both side and also the foreign key.

    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.

    • Marked as answer by Alan Wheeler Wednesday, December 10, 2014 10:00 PM
    Wednesday, December 10, 2014 3:44 AM
    Moderator
  • Hi Darnold,

    Thanks for your response.  I'd wanted to follow up with you in case there’s something I might be missing.

    You’re using Blogs and Posts for the entities in your response; am I correct this comes from the ubiquitous MSDN Data Developer Center example regarding EF?  Assuming so, that's fine, but it required a little translation for me.  So I tried the following but this doesn't work:

        Dim allOrders As New List(Of Order)
        Using db As New ApplicationDbContext
          allOrders = db.orders.Include(Function(b) b.products).ToList()
        End Using

    Visual Studio seems to indicate the parameter for Include() has to be a string, but I’ve learned it’s never good to make assumptions, so I wanted to check with you and make sure I’m not missing something.  I even saw the line you quoted from the MSDN page that shows “.Include(b => b.Posts)”, so I don’t understand why I couldn’t do that as above.

    The For Each loop you recommended as shown below worked great (using the line from Fred’s post):

        Dim allOrders As New List(Of Order)
        Using db As New ApplicationDbContext
          allOrders = db.orders.Include("Products").ToList()
          For Each Order As Order In db.orders
            For Each prod As Product In Order.Products
              Dim name = prod.name
            Next
          Next
        End Using

    I won't duplicate here my comments to Fred about the mistake in my previous thinking.

    Your reminder to reread the Loading Related Entities page on MSDN was very useful.  Thanks.  I had read that previously, and the Microsoft Step by Step book I studied on EF also covered it after I went back and looked, but I didn’t comprehend the nuances until both of your posts today pointed me to my problems.

    If one of you could please also take a look at my other post below with some follow up questions if you don’t mind.

    Again, as always, thank you very much.

    Best Regards,
    Alan

    Wednesday, December 10, 2014 9:48 PM
  • Hi Fred,
    That worked perfectly for the question I asked.  Of course as it always turns out, the question I asked didn't really get to the bottom of my issues.  But regarding my specific question...

    I knew I should be able to write a simple LINQ to access the data, but I forgot you have to use Include in LINQ just like in SQL.  While I do know SQL, I’m best categorized as a beginner, so my data access instincts aren’t fully in place.

    You also said above I didn’t need to use LINQ query, but isn’t the line you gave me a LINQ query?  I just want to make sure I'm not misunderstanding the terminology.

    I also wanted to follow up on your comment regarding virtual (C#) / Overridable (VB).  Please refer to my other response below.

    Thanks again.

    Best Regards,
    Alan


    • Edited by Alan Wheeler Wednesday, December 10, 2014 9:53 PM minor edit
    Wednesday, December 10, 2014 9:53 PM
  • Hi Darnold and Fred,

    As usual, you guys are best.  This was a big thread for me.  Although I made specific posts to each of your replies, I’d like to wrap up with several questions for confirmation.  I know it’s 5 questions, but the first 3 are hopefully Yes/No unless you have a specific comment.  This should wrap this thread up pretty thoroughly.  Does it need a different title?

    Q1: Does eager loading mean all dependent tables are loaded at the same time as the parent? 

    The terminology was a little confusing because everyone uses the word ‘automatic’ when speaking about Lazy loading, and if I understand Eager loading correctly, I think of Eager loading as automatically making all the entities available, and Lazy requiring a specific access request, which for me doesn’t sound automatic, but I can deal with it, just need confirmation.

    Q2: Does eager loading always require an Include() method to access a dependent class/table?

    Q3: As defined in my initial question, are my entities eager loading because they didn’t have Overridable for the Property Products in Class Order?  It seems so.  When I changed as below

    Public Overridable Property Products As New List(Of Product)

    Then the following code worked to display all the products:

    allOrders = db.orders.ToList()

    Q4: What is the primary reason I would define a DbSet for a dependent table?  It doesn’t seem to be necessary.  In my study after both of your replies most of the examples explicitly create a DbSet for both the parent and the dependent table, but I don’t understand why that is valuable; unless access to that table independently was desired?

    Q5: In my study I also came across IQueryable and ICollection in all the examples.  I have used List thinking it was a type that includes all of the characteristics or attributes of IQueryable which includes all of ICollection.  Is my understanding of List incorrect, and should I be using IQueryable or ICollection, and why?

    Thanks again for your help with this.

    Best Regards,
    Alan

    Wednesday, December 10, 2014 9:59 PM
  • Visual Studio seems to indicate the parameter for Include() has to be a string, but I’ve learned it’s never good to make assumptions, so I wanted to check with you and make sure I’m not missing something.  I even saw the line you quoted from the MSDN page that shows “.Include(b => b.Posts)”, so I don’t understand why I couldn’t do that as above.

    I don't know. It was taken out of the posted link and converted over to VB.NET. The other queries in the link or using string value for the include. I have always used a string value. So maybe the guy's example is wrong.

    Thursday, December 11, 2014 5:05 PM