locked
Map One-To-Many relationship in a single entity RRS feed

  • Question

  • Hi,

    Is it possible to map two tables ( that are related togeath in a one-to-many relationship) to a single entity?

    For example,

    I've a table called Restaurant with the following columns:

    RestaurantID, INT,IDENTITY, PrimaryKey
    RestaurantName VARCHAR(100)

    And another table, called RestaurantItem with the following columns:

    RestaurantItemID, INT,IDENTITY, PrimaryKey
    RestaurantItemName VARCHAR(100)
    RestaurantID INT ( Foreign Key, References Restaurant(RestaurantID) )

    Now, is it possible to combine the two tables into a single entity?

    Sunday, February 5, 2012 3:17 PM

Answers

All replies

  • Hi ahmedhse;

    If I understand you correctly you want all the fields of both tables in the database to be mapped to a single entity. If what I stated is what you are looking for then the answer is no. The reason is that the table RestaurantItem is the many side of Restaurant table and therefore RestaurantItem has one or more records with the same fields names that could not be represented in a single class.

    What you can do is query the Restaurant table and return the Restaurant table and its related RestaurantItem as a collection of RestaurantItem in Restaurant some thing like the following query.

    var results - ObjectContext.Restaurant.Include("RestaurantItems").Select( r => r).ToList();

    The variable results will be a collection of Restaurant and each Restaurant has a collection of RestaurantItem.

    I hope that helps.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, February 5, 2012 7:43 PM
  • Thanks Fernando for your answer.

    You stated that:

    The reason is that the table RestaurantItem is the many side of Restaurant table and therefore RestaurantItem has one or more records with the same fields names that could not be represented in a single class

    So, suppose that I'de like to get a collection of different columns from the two entities, for example an entity that looks like this:

    RestaurantItemID
    RestaurantItemName
    RestaurantID
    RestaurantName

    In this case, there are no fields with the same name. Is it possible now to achieve this goal?

    I need the result in this shape because I've a stored procedure that gathers this data from the DB, that stored procedure is mapped to a function import in the context, that function import is called from a WCF service method, and the result of that method call is displayed on a GridView control on the aspx file via a


    Also, I need this entity type to be used in other business scenarios, such as insert of new restaurant items and updating existing items.

    I tried handling this via Complex Type, but I didn't find any means for making operations other than data collection.


    • Edited by ahmedhse Monday, February 6, 2012 6:47 AM
    Monday, February 6, 2012 5:46 AM
  •  

    Can you post the Stored procedure so we can see how the result set is being created.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, February 6, 2012 2:36 PM
  • Here it is:

    CREATE PROCEDURE GetRestaurantItem
    	@RestaurantID		INT
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	SELECT
    		RestaurantItem.RestaurantItemID
    		,RestaurantItem.RestaurantItemName
    		,RestaurantItem.RestaurantID
    		,Restaurant.RestaurantName
    	FROM
    	RestaurantItem	
    	INNER JOIN Restaurant
    		ON RestaurantItem.RestaurantID = Restaurant.RestaurantID
    	WHERE
    		RestaurantItem.RestaurantID = @RestaurantID
    END
    • Edited by ahmedhse Monday, February 6, 2012 3:11 PM
    Monday, February 6, 2012 3:09 PM
  • Hi ahmedhse;

    To your question, "Is it possible to map two tables ( that are related togeath in a one-to-many relationship) to a single entity?", Because this is being done by a stored procedure it is taking care of the shape of the result set and returning a single row of data for each joined table row. This can be accomplished as follows.

    Import the Stored Procedure into the EF model, when you do the Function Import of the Stored Procedure at the bottom of that dialog box click on the button "Get Column Information" and once that information appears in the text box click on the button "Create New Complex Type" then click on the "OK" button., Create the ObjectContext and then execute the procedure.

    //Create the ObjectContext and then execute the procedure.
    ObjectContextName ctx = new ObjectContextName();

    var results = ctx.GetRestaurantItem( parameter ).ToList();

    At this point results holds a List of something most-likely called GetRestaurantItem_Results that can be found in the model.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, February 6, 2012 4:10 PM
  • Ok, I am sure now what I'd like to do cannot be done using EF in the same way it was done in the GREAT DataSet!

    I've been investigating that problem for the last 4 days, and no direct solution for now. The only INDIRECT way I've found to build an entity that represents the one-to-many relationship in the way I described above ( and to be able to make data modifications as well )  is to

    1. Make a view in the DB
    2. Map that view to an Enity
    3. Create Function Import the SP above
    4. Map the Function Import to the Entity in step 2
    5. Define the stored procedures for data modifications
    6. Map the procedure in step 5 to the Modification Functions of the Entity in step 2

    Actually, Entity Framework seemed to be the SMARTEST solution ever for everything in data processing. But when it comes to real-time work, it is found to be LESS SMART in handling some very common scenarios in every-day developer tasks!

    It offers a very strong framework for handling client data processing against data-store data ( via the Context and the EntityStateManger ). But, unfortunately, it lakes the flixibility of the data set in building the data structure in a custom ( unmapped ) way to handle the business needs according to the developer mind and thoughts!

    The example above shows some sort of weakness in the 6 years old , 4+ generations, EF!



    • Edited by ahmedhse Tuesday, February 7, 2012 5:50 AM
    Tuesday, February 7, 2012 5:48 AM
  • Finally,

    I've found something that could help a little:

    http://msdn.microsoft.com/en-us/library/cc982038.aspx

    • Marked as answer by ahmedhse Wednesday, February 8, 2012 8:45 AM
    Wednesday, February 8, 2012 8:45 AM
  • I know your question is pretty old, but since it was the top Bing result, I'll post an answer here.

    You need to override OnModelCreating in your DBContext class like so:

    protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Restaurant>() .Map(m => { m.Properties(t => new { t.RestaurantID, t.RestaurantName }); m.ToTable("Restaurants"); }) .Map(m => { m.Properties(t => new { t.RestaurantItemName, t.RestaurantId }); m.ToTable("RestaurantItems"); }); }

    And for your Restaurant class:

    public class Restaurant
    {
      public Restaurant() {}
      public int RestaurantID { get; set; }
      public string RestaurantName { get; set; }
      public string RestaurantItemName { get; set; }
    }

    Note the order of the map. You must map your "primary" table first and then your "foreign" table. This way inserts will work properly.

    Hope this helps. It worked great for us when connecting EF to some legacy tables.

    Friday, October 3, 2014 8:43 PM