locked
Multiple tables in a single entity RRS feed

  • Question

  • Just starting to learn teh entity framework "code first", and while I appreciate the samples and tutorials, I find I am struggling a bit with real world applications.  For example, I currently have a set of tables with a one to many relationship (product p => productDetails pd and product p => productPriceHistory pph).  I have a query that pulls back the product details and the price history for that product over the last three months:

    p.ProductName  pd.Description pd.CurrentPrice pph.PoductPricePointA pph.ProductPricePointB

    Juice                   Apple Juice      $2.79                 $2.49                              $2.22

    How can I create a single entity to store the values for the columns indicated above?

    Wednesday, June 29, 2011 9:36 PM

Answers

  • Hi fha101,

    You can use Join method to retrieve the Anonymous Type then transform them to Type:

     class Program
      {
        static void Main(string[] args)
        {
          using (var context= new EFTestContext())
          {
            var test = from e in context.Employees
                  join v in context.Vacations on e.EmployeeId equals v.EId
                  select new { EId = e.EmployeeId, Days = v.Days };
            List<ComplexType> list = new List<ComplexType>();
            foreach (var item in test)
            {
              list.Add(new ComplexType() { EId=item.EId, Days=item.Days });
            }
          }
        }
      }
      public class ComplexType
      {
        public int EId { get; set; }
        public int Days { get; set; }
      }
    

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Monday, July 11, 2011 11:10 AM
    Tuesday, July 5, 2011 8:31 AM

All replies

  • Hi fha101,

    Welcome!

    According to your description, you can use Anonymous type to store the records like: Select(c=>new{ProductName=c.ProductName,···}), and you can also use Complex type in Code First, you can refer here: http://blogs.msdn.com/b/adonet/archive/2011/01/30/using-dbcontext-in-ef-feature-ctp5-part-5-working-with-property-values.aspx

    Have a nice day. 


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 30, 2011 11:19 AM
  • Hello, Thanks for the response, but accessing properties is really not my issue. I need to know how to create a an entity from two or more tables. For example, as per above I have three tables: Product, ProductDetails, ProductPriceHistory. I want to create an entity that represents columns from all three tables sort of like this
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.ComponentModel.DataAnnotations;
    
    namespace POS.Models
    {
      public class FullProductDetails
      {
       public int ProductID { get; set; }
       public string ProductDescription { get; set; }
       public double ProductCurrentPrice { get; set; }
       public double PoductPricePointA { get; set; }
       public double PoductPricePointB { get; set; }
      }
    }
     
    
    

     I can create the class without issue of course, but then, how do I map three tables to it so I can retrieve the values (using dbContext of course)?

    Thanks in advance.

    Thursday, June 30, 2011 4:28 PM
  • Hi fha101,

    You can use Join method to retrieve the Anonymous Type then transform them to Type:

     class Program
      {
        static void Main(string[] args)
        {
          using (var context= new EFTestContext())
          {
            var test = from e in context.Employees
                  join v in context.Vacations on e.EmployeeId equals v.EId
                  select new { EId = e.EmployeeId, Days = v.Days };
            List<ComplexType> list = new List<ComplexType>();
            foreach (var item in test)
            {
              list.Add(new ComplexType() { EId=item.EId, Days=item.Days });
            }
          }
        }
      }
      public class ComplexType
      {
        public int EId { get; set; }
        public int Days { get; set; }
      }
    

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Monday, July 11, 2011 11:10 AM
    Tuesday, July 5, 2011 8:31 AM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 11, 2011 5:53 AM
  • Hello,

    Was away for a bit and this is the first chance I had to read the last response.  I will let you know as soon as I can.

    Cheers,

    Dave

    Monday, July 11, 2011 8:10 PM
  • This worked really well.  Thanks for the help.  One question I do have however... this complex class is intended to display the data it holds, but (for security reasons) I don't want any of the  properties updatable via binding.  I know I can use include or exclude lists, but this seems a little cumbersome since I want the entire type excluded.  Is there a simple method of doing this, or do I need to use an exclude list.
    Tuesday, July 12, 2011 4:24 PM