none
How to fetch master detail data using EF and LINQ RRS feed

  • Question

  • suppose i have a order and order detail entity. i want to fetch specific order data and its related order detail by EF.

    can i use this LINQ query

    IEnumerable<Order> order= _ctx.order
                .Include(x => x.orderdetails)
                .Where(x => x.OrderID== _OrderID).ToList();

    does the above code works fine? also tell me how to achieve the same with join also?

    also tell me if i need to show specific customer data and customer order and order details then how i need to compose my EF query? help me with code. thanks

    Thursday, January 4, 2018 11:30 AM

Answers

  • Hi Sudip_inn,

    >>does the above code works fine?

    If you use entity framework 6, please modify lambda expression to string in Include method. like below. if you use entity framework core, it works fine.

    IEnumerable<Order> order= _ctx.order
                .Include("orderdetails")
                .Where(x => x.OrderID== _OrderID).ToList();

    >>also tell me how to achieve the same with join also?

    If master and detail model have navigate property, we don't need use join, Entity Framework supports three ways to load related data - eager loading, lazy loading and explicit loading, For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/jj574232%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

    >>also tell me if i need to show specific customer data and customer order and order details then how i need to compose my EF query? help me with code. thanks

    var result = from m in _ctx.order
                 join d in _ctx.orderdetail on m.OrderId equals d.OrderId 
                select new
                {
                      OrderId = m.OrderId,
                      Name = m.Name,//other field
                      Posts = m.Posts
                };

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, January 5, 2018 2:47 PM
    Friday, January 5, 2018 2:53 AM
    Moderator

All replies

  • Hi Sudip_inn,

    >>does the above code works fine?

    If you use entity framework 6, please modify lambda expression to string in Include method. like below. if you use entity framework core, it works fine.

    IEnumerable<Order> order= _ctx.order
                .Include("orderdetails")
                .Where(x => x.OrderID== _OrderID).ToList();

    >>also tell me how to achieve the same with join also?

    If master and detail model have navigate property, we don't need use join, Entity Framework supports three ways to load related data - eager loading, lazy loading and explicit loading, For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/jj574232%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

    >>also tell me if i need to show specific customer data and customer order and order details then how i need to compose my EF query? help me with code. thanks

    var result = from m in _ctx.order
                 join d in _ctx.orderdetail on m.OrderId equals d.OrderId 
                select new
                {
                      OrderId = m.OrderId,
                      Name = m.Name,//other field
                      Posts = m.Posts
                };

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, January 5, 2018 2:47 PM
    Friday, January 5, 2018 2:53 AM
    Moderator
  • Your example should should theoretically work. Here's an example using join with syntax-based query in EF:

    Hide   Copy Code
    var order = (from o in _ctx.order
                     join od in _ctx.orderdetails on o.OrderID equals od.OrderID
                     where o.OrderID == _OrderID).ToList();


    To select specific fields, you could either use an anonymous type object like this:

    Hide   Copy Code
    var order =  _ctx.order
                .Include(x => x.orderdetails)
                .Where(x => x.OrderID== _OrderID)
                .Select(x => new
                {
                     OrderID = x.OrderID,
                     SomeColumn = x.SomeColumn1,
                     SomeColumn2 =  x.SomeColumn1
                }).ToList();  


    Or use a strongly-typed object like this:

    Hide   Copy Code
    var order =  _ctx.order
                .Include(x => x.orderdetails)
                .Where(x => x.OrderID== _OrderID)
                .Select(x => new OrderDetail
                {
                     OrderID = x.OrderID,
                     SomeColumn = x.SomeColumn1,
                     SomeColumn2 =  x.SomeColumn1
                }).ToList();  


    Noticed that the Select clause now use the OrderDetail. The OrderDetail is just a class that holds the properties that you want to use in your query.


    Saturday, January 6, 2018 6:04 PM