locked
can not add association with non-primary key in EF RRS feed

  • Question

  • vs2010 sp1

    CREATE TABLE [dbo].[EmployeeTB](
        [EmployeeID] [varchar](13) NOT NULL,
        [EmployeeName] [varchar](20) NOT NULL,
        [TypeACodeID] [varchar](4) NOT NULL,
        [TypeBCodeID] [varchar](4) NOT NULL
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[EmployeeTB] WITH NOCHECK ADD
      CONSTRAINT [PK_EmployeeTB] PRIMARY KEY CLUSTERED
      ([EmployeeID]) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[TypeTB](       --- primary key is TypeID + CodeID
        [TypeID] [varchar](4) NOT NULL,
        [TypeName] [varchar](20) NOT NULL,
        [CodeID] [varchar](4) NOT NULL,
        [CodeName] [varchar](20) NULL,
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[TypeTB] WITH NOCHECK ADD
      CONSTRAINT [PK_TypeTB] PRIMARY KEY CLUSTERED
      ([TypeD],[CodeID]) ON [PRIMARY]

    TypeDB has 4 records
    A, ATYPE_NAME, 1, ATYPE_CODE1
    A, ATYPE_NAME, 2, ATYPE_CODE2
    B, BTYPE_NAME, 1, BTYPE_CODE1
    B, BTYPE_NAME, 2, BTYPE_CODE2

    EmployTB has 2 records
    001, 001Name,  1,  1        --->  ATYPE_CODE1,  BTYPE_CODE1
    002, 002Name,  2,  2        --->  ATYPE_CODE2,  BTYPE_CODE2

    I have try add Entity and give base type to TypeTB and name Type001TB and in table mapping add condition filter

    TypeID = 001,  and then add Entity and give base type to TypeTB and name Type002TB

    but Type001TB and Type002TB can not create association with EmployeeTB.


    how can I add association for EmployeeTB and TypeTB or Type001TB ?

    thanks in advance.

     

    Friday, June 3, 2011 2:12 AM

Answers

  • Hi again!

    I cannot see the reason not having the TypeId in the employee table even if they are constant, you can't create a FK in the database either if you don't have both columns as FK columns in EmployeeTB, and your code will get more complicated and error prone.

    As for your code I'm afraid, based on your database design solution, that you can't optimize it any further. What you could do to make it cleaner, if your TypeTB doesn't contain a lot of entries, is to fetch the whole table with a filter of "001" and instead do a manually join in your second query. Like this:

    var result = employees.ToList();
    
    var filterTypes = types.Where(type => type.TypeId == "001").ToList();
    
    result.ForEach(emp => emp.ACodeName = filterTypes.Where(type => type.CodeID == emp.TypeACodeId)
                             .Select(type => type.CodeName).FirstOrDefault());
    
    

    It makes your code be a little bit cleaner. You can also filter the types a bit more by first fetching the employees you want to fetch, and then use these as a filter on your types table before merging them with the ForEach loop. Like this:

    var filterTypes = types.Where(type => type.TypeId == "001" && result.Where(emp => emp.TypeACodeId == type.CodeId).Any()).ToList();
    
    

    The drawback by doing my example is that it is your code, not the database engine that does the actual job.


    --Rune
    • Proposed as answer by Jackie-Sun Friday, June 3, 2011 1:34 PM
    • Marked as answer by tsai0303cn Thursday, June 16, 2011 8:48 AM
    Friday, June 3, 2011 9:03 AM

All replies

  • Hi,

    Since your TypeDB table has two primary keys, you need two FK columns in your EmployeeTB to be able to map both primary keys, you cannot have a FK relationship on only one of the primary keys, since this makes your data inconsistent. It's also breaks the the rules of good database design.

    So, you need to add both TypeATypeID and TypeBTypeID to your EmployeeTB table, then you can have FKs and associations between the tables.

    Hope this helps!


    --Rune
    Friday, June 3, 2011 8:00 AM
  • thanks Rune

    Because EmployeeTB's TypeATypeID and TypeBTypeID are always constant, so not add them to tables field.

     

    I use join method now, it work fine,  but syntax is too complex.

    public IQueryable < EmployeeTB > GetEmployeeTB()
            {
                ObjectSet<EmployeeTB> employees = ObjectContext.EmployeeTB;
                ObjectSet<TypeTB> types = ObjectContext.TypeTB;
                var q1 = employees.Join(
                    types,
                    employee => new { "001",  employee.TypeACodeID },
                    type => new { type.TypeID,  type.CodeID },
                    (employee, type) => new
                    {
                        ACodeName = type.CodeName,    // ACodeName is in metadata not in table field
                        Employee = employee
                    });


                var q2 = q1.ToList().Select(emp => new EmployeeTB()
                    {
                        Emp_no = emp.Emp_no,
                        ...  table has forty fields

                        ...  so missing one or two field sometimes
                        ACodeName = emp.ACodeName,
                    }).AsQueryable();
                return q2;
            }

    Is anothor way can simplify code above ?

    thanks in advance.

     

    Friday, June 3, 2011 8:23 AM
  • Hi again!

    I cannot see the reason not having the TypeId in the employee table even if they are constant, you can't create a FK in the database either if you don't have both columns as FK columns in EmployeeTB, and your code will get more complicated and error prone.

    As for your code I'm afraid, based on your database design solution, that you can't optimize it any further. What you could do to make it cleaner, if your TypeTB doesn't contain a lot of entries, is to fetch the whole table with a filter of "001" and instead do a manually join in your second query. Like this:

    var result = employees.ToList();
    
    var filterTypes = types.Where(type => type.TypeId == "001").ToList();
    
    result.ForEach(emp => emp.ACodeName = filterTypes.Where(type => type.CodeID == emp.TypeACodeId)
                             .Select(type => type.CodeName).FirstOrDefault());
    
    

    It makes your code be a little bit cleaner. You can also filter the types a bit more by first fetching the employees you want to fetch, and then use these as a filter on your types table before merging them with the ForEach loop. Like this:

    var filterTypes = types.Where(type => type.TypeId == "001" && result.Where(emp => emp.TypeACodeId == type.CodeId).Any()).ToList();
    
    

    The drawback by doing my example is that it is your code, not the database engine that does the actual job.


    --Rune
    • Proposed as answer by Jackie-Sun Friday, June 3, 2011 1:34 PM
    • Marked as answer by tsai0303cn Thursday, June 16, 2011 8:48 AM
    Friday, June 3, 2011 9:03 AM