Basic question relations like n-n and 1-n

Topics: General, Writing modules
Jul 27, 2011 at 6:16 PM

I followed the tutorial http://orchardproject.net/docs/Creating-1-n-and-n-n-relations.ashx#Building_an_iN-Ni_Relationship_34

Everything went well but i still wondering if the relations i created are "virtual" and managed by Orchard or the database? What i mean with virtual is that normally in sql server you create relations by creating foreign keys, primary keys, etc. So to sum this up: Will the relations from this tutorial be generated to (in case of sql server) FK's and PK's or are they just virtual and what is the reason for that when they are only virtual?

Coordinator
Jul 27, 2011 at 6:22 PM

You can add foreign keys from your migration but it won't be automatic, no.

Jul 27, 2011 at 8:04 PM

ok but i mean: are the relations which are created and example of that tutorial maintained by code or database? If by code: what is the reason the team has choosen for this solution because i thought native database relations are way faster.

Coordinator
Jul 27, 2011 at 8:06 PM

Code: foreign keys don't get added unless you add them yourself in the migration. Why? Lack of time to deal with the complications that adds.

Jul 28, 2011 at 6:17 AM
Edited Jul 28, 2011 at 6:31 AM

Yes time is unfortunately always an issue. After reading this tutorial once again i understand the logic. I thought that the code fully maintained the relations but that's not true. The code only determines how to retrieve the data from the earlier created relations in database in the migrations. Am i right?

Coordinator
Jul 28, 2011 at 6:39 AM

Just add them in your migrations and be done with it.

Nov 28, 2011 at 10:19 AM

Hi,

I was wondering the same thing as Znowman was: "virtual" or managed by the database.

Thank you Bertrand for your answers but could you please explain how to add them in the migrations?

I have this:

public int Create()
        {
            SchemaBuilder.CreateTable("AttributeRecord", table => table
               .Column<int>("Id", column => column.PrimaryKey().Identity())
            );
            
            SchemaBuilder.CreateTable("AttributeValueRecord", table => table
               .Column<int>("Id", column => column.PrimaryKey().Identity())
            );

            SchemaBuilder.CreateTable("AttributeHasValuesRecord", table => table
              .Column<int>("Id", column => column.PrimaryKey().Identity())
              .Column<int>("AttributeRecord_Id") // foreign key
              .Column<int>("AttributeValueRecord_Id") // foreign key
           );
            
            return 1;
        }

public class AttributeRecord
    {
        public virtual int Id { get; set; }
    }

public class AttributeValueRecord
    {
        public virtual int Id { get; set; }
    }

public class AttributeHasValuesRecord
    {
        public virtual int Id { get; set; }

        public virtual AttributeRecord AttributeRecord { get; set; }
        public virtual AttributeValueRecord AttributeValueRecord { get; set; }
    }

What do I have to change?

Thank you

Nov 28, 2011 at 12:46 PM

Nevermind. After some research I found how to do it:

 

public int UpdateFrom1()
        {
            SchemaBuilder.CreateForeignKey("FK_Attribute",
                "AttributeHasValuesRecord", new string[] {"AttributeRecord_Id"},
                "AttributeRecord", new string[] { "Id" });

            SchemaBuilder.CreateForeignKey("FK_AttributeValue",
                "AttributeHasValuesRecord", new string[] { "AttributeValueRecord_Id" }, 
                "AttributeValueRecord", new string[] { "Id" });

            return 2;
        }

Nov 28, 2011 at 5:22 PM

yes i read that you can create foreignkeys like this but what is the benefit? is it faster? because without the foreignkeys the relations works also.

Coordinator
Nov 28, 2011 at 9:37 PM

The benefit is integrity. You can create indices as well for perf.

Nov 29, 2011 at 7:14 AM
Edited Nov 29, 2011 at 7:41 AM

Thankss! It would be nice though if the creation of foreignkeys are included in the n-n relation tutorial.

Nov 29, 2011 at 12:53 PM

The tutorial is on wiki, anybody can update that ;)

Oct 8, 2012 at 1:23 PM

Hello Gwena, monsieur as per your above discussions, its been tried to create the migration without using of command line approach, rather manually written the code to make the appropriate and conventional n-n relationship (using primary and foreign keys), after writing the whole code then successfully built and run it. Its executed properly and entered the auto-entry in .csproj file for migrations of that module besides failed to be fabricated the tables in database. So please suggest me for the same for sake of my project. Its really very urgent. Thanks in advance.   

Coordinator
Oct 10, 2012 at 12:30 AM

You'll have to give more information. You are not saying how it fails, what you tried and what you expected.

Oct 11, 2012 at 10:03 AM
Edited Oct 11, 2012 at 10:23 AM

Yes, to illustrate the previous version of my Post, in this m gonna updating this post with complete code ,

to make perfect or more integrated relationship, So as follows,

Detail View Of My post 

First i code for models with cs files with these properties :

Like

Declaration for Facility Class

  public virtual Int32 FacilityId { get; set; }
        public virtual Int32 FacilityUserId { get; set; }
        public virtual Int32 CreatorID { get; set; }
        public virtual String Name { get; set; }
        public virtual String Address { get; set; }
        public virtual Int32 Phone { get; set; }
        public virtual String Email { get; set; }
        public virtual String Password { get; set; }
        public virtual bool IsActive { get; set; }
        public virtual String DataCreated { get; set; }

Definition in Facility Class

        public Int32 FacilityId { get { return Record.FacilityId; } set { Record.FacilityId = value; } }
        public Int32 FacilityUserId { get { return Record.FacilityUserId; } set { Record.FacilityUserId = value; } }
        public Int32 CreatorID { get { return Record.CreatorID; } set { Record.CreatorID = value; } }
        public String Name { get { return Record.Name; } set { Record.Name = value; } }
        public String Address { get { return Record.Address; } set { Record.Address = value; } }
        public Int32 Phone { get { return Record.Phone; } set { Record.Phone = value; } }
        public String Email { get { return Record.Email; } set { Record.Email = value; } }
        public String Password { get { return Record.Password; } set { Record.Password = value; } }
        public bool IsActive { get { return Record.IsActive; } set { Record.IsActive = value; } }
        public String DateCreator { get { return Record.DateCreator; } set { Record.DateCreator = value; } }

like wise there are many more classes in models

 Then in Migrations.cs class, we manually typed the Migration code as written below so as you suggested in your post.

Without using the Command in command line.

       "codegen datamigration PatientFacility"

 

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Orchard.ContentManagement.MetaData;
using Orchard.Core.Contents.Extensions;
using Orchard.Data.Migration;
using Orchard.Environment.Extensions;
using PatientFacility.Models;

namespace PatientFacility
{
    public class Migrations : DataMigrationImpl
    {
        public int create()
        {
            SchemaBuilder.CreateTable("tblFacility", table => table.Column<Int32>("FacilityId", column => column.PrimaryKey().Identity().NotNull())
                .Column<Int32>("FacilityUserID", column => column.NotNull())
              .Column<Int32>("CreatorID", column => column.Nullable())
              .Column<String>("Name", column => column.Nullable())
              .Column<String>("Address", column => column.Nullable())
              .Column<Int32>("Phone", column => column.Nullable())
              .Column<String>("Email")
              .Column<String>("Password")
              .Column<bool>("IsActive")
              .Column<String>("DateCreated", column => column.Nullable())
              );
            SchemaBuilder.CreateTable("tblNurse", table => table.Column<Int32>("NurseID", column => column.PrimaryKey().Identity().NotNull())
             .Column<Int32>("NurseUserID", column => column.Nullable())
             .Column<Int32>("CreatorID", column => column.Nullable())
             .Column<String>("Name", column => column.Nullable())
             .Column<String>("Address", column => column.Nullable())
             .Column<Int32>("Phone", column => column.Nullable())
             .Column<String>("Email", column => column.Nullable())
             .Column<String>("Password", column => column.Nullable())
             .Column<bool>("IsActive")
             .Column<String>("CreatedDate", column => column.Nullable())
             );

           return 1;

}

But its not creating any Data BASE Objects(Tables) even not making an entry in "dbo.Orchard_Framework_DataMigrationRecord" of New Migration of PatientFacility (My Module), as i m expecting from this after running this code completely. Please tell me what to so , if you need any more information , then please ask. Your advise will be appreciated. Thanks in advance.

Coordinator
Oct 11, 2012 at 3:54 PM

Did you enable the feature?

Oct 12, 2012 at 5:21 AM
Edited Oct 12, 2012 at 5:41 AM

Yes, I enabled the Feature Like  with Command Line  "Feature enable PatientFacility" and PatientFacility a module name on which am working on. One More thing After doing this Procedure , Migration wasn't created But After i did with Command Line , So its created. But i want to make it by doing Primary key and foreign key So that it. Please advise.

Coordinator
Oct 12, 2012 at 6:21 AM

Anything in app_data\logs?

Oct 12, 2012 at 3:49 PM
Edited Oct 12, 2012 at 3:49 PM

Na, I didn't put any thing in app_data\logs. Do Suggest More.... Its Urgent.

Coordinator
Oct 12, 2012 at 4:35 PM

I'm not asking if you put anything in there, but if there *is* anything in there. The log files in there should give you more details about what happened.

Another thing you can do is start with a new site or rollback your module's migration state in the database, then put a breakpoint in the migration and see if it gets hit, and then if you can see any exception happening.