none
Columns with strange names - how to map with LINQ? RRS feed

  • Question

  • I get a database with some strange columns names like:

    TABLE Customer(
        primary_key_of_table_customer int NOT NULL,
        name_of_customer vachar(60) NOT NULL
    )

    TABLE Order (
        primary_key_of_table_order int NOT NULL,
        foreing_key_of_table_customer int NOT NULL,
        date_of_order datetime NOT NULL
    )

    But I dont want to use that column name in my class, like:

    Customer c = new Customer();
    c.Primary_key_of_table_customer = 10;

    However LINQ requires that property and column have same name!!!

    I can do this including Name in Column:

    [Column(Name = "foreing_key_of_table_customer" Storage = "CustomerId")]
    public CustomerId {....} //class order

    But how can I do this in Association?

    [Association(Storage = "_Customer", ThisKey = "?????", OtherKey = "????")]
    public Customer Customer {.....} //class order

    Thanks for help!
    Tuesday, April 6, 2010 2:59 PM

Answers


  • However LINQ requires that property and column have same name!!!

     

    No, it doesn't*. You can give the properties a different name than the column in the designer (or code if you hand-code your classes).

    E.g.:

    [Table(Name="[Order]")]
    public class Order
    {
    ...
      [Column(Name="primary_key_of_table_order", Storage="_OrderID", DbType="Int NOT NULL", IsPrimaryKey=true)]
      public int OrderID { ... }
    
      [Column(Name="foreign_key_of_table_customer", Storage="_CustomerID", DbType="Int NOT NULL")]
      public int CustomerID { ... }
    
      [Association(Name="Customer_Order", Storage="_Customer", ThisKey="CustomerID", OtherKey="CustomerID", IsForeignKey=true)]
      public Customer Customer { ... }
    }
    
    [Table(Name="[Customer]")]
    public class Customer
    {
    ...
      [Column(Name="primary_key_of_table_customer", Storage="_CustomerID", DbType="Int NOT NULL", IsPrimaryKey=true)]
      public int CustomerID { ... }
    
      [Column(Name="name_of_customer", Storage="_Name", DbType="VarChar(60) NOT NULL", CanBeNull=false)]
      public string Name { ... }
    
      [Association(Name="Customer_Order", Storage="_Orders", ThisKey="CustomerID", OtherKey="CustomerID")]
      public EntitySet<Order> Orders { ... }
    }

    * = with one exception; autogenerated columns of certain types (e.g. guid PKs with newid()/newsequentialid()) must have the same property and column name in .net 3.5 due to a bug. [that is fixed in .net 4.0]


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Marked as answer by venier Friday, April 9, 2010 12:59 PM
    • Unmarked as answer by venier Friday, April 9, 2010 2:26 PM
    • Marked as answer by Alex LiangModerator Tuesday, April 13, 2010 7:18 AM
    Wednesday, April 7, 2010 3:39 AM
    Answerer

  • Will I need to add a Column to foreign key as code above?

    Yes, all properties that map to DB columns need a column attribute.
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Saturday, April 10, 2010 2:52 AM
    Answerer

All replies

  • Why not create a partial class with the names you prefer as public properties which simply map to the original names. I discuss it on my blog entitled:

    Tribal Knowledge: Linq Entities To Do Your Bidding via the Partial Keyword in C# . HTH


    William Wegerson (www.OmegaCoder.Com )
    Tuesday, April 6, 2010 8:18 PM
    Moderator

  • However LINQ requires that property and column have same name!!!

     

    No, it doesn't*. You can give the properties a different name than the column in the designer (or code if you hand-code your classes).

    E.g.:

    [Table(Name="[Order]")]
    public class Order
    {
    ...
      [Column(Name="primary_key_of_table_order", Storage="_OrderID", DbType="Int NOT NULL", IsPrimaryKey=true)]
      public int OrderID { ... }
    
      [Column(Name="foreign_key_of_table_customer", Storage="_CustomerID", DbType="Int NOT NULL")]
      public int CustomerID { ... }
    
      [Association(Name="Customer_Order", Storage="_Customer", ThisKey="CustomerID", OtherKey="CustomerID", IsForeignKey=true)]
      public Customer Customer { ... }
    }
    
    [Table(Name="[Customer]")]
    public class Customer
    {
    ...
      [Column(Name="primary_key_of_table_customer", Storage="_CustomerID", DbType="Int NOT NULL", IsPrimaryKey=true)]
      public int CustomerID { ... }
    
      [Column(Name="name_of_customer", Storage="_Name", DbType="VarChar(60) NOT NULL", CanBeNull=false)]
      public string Name { ... }
    
      [Association(Name="Customer_Order", Storage="_Orders", ThisKey="CustomerID", OtherKey="CustomerID")]
      public EntitySet<Order> Orders { ... }
    }

    * = with one exception; autogenerated columns of certain types (e.g. guid PKs with newid()/newsequentialid()) must have the same property and column name in .net 3.5 due to a bug. [that is fixed in .net 4.0]


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Marked as answer by venier Friday, April 9, 2010 12:59 PM
    • Unmarked as answer by venier Friday, April 9, 2010 2:26 PM
    • Marked as answer by Alex LiangModerator Tuesday, April 13, 2010 7:18 AM
    Wednesday, April 7, 2010 3:39 AM
    Answerer
  • [Table(Name="[Order]")]
    public class Order
    {
    ...
      [Column(Name="foreign_key_of_table_customer", Storage="_CustomerID", DbType="Int NOT NULL")]
      public int CustomerID { ... }
    }
    
    
    Thanks!!!

    now is running, I used a private attribute instead of the property to reference ThisKey and OtherKey, but now I got a new question.

    Will I need to add a Column to foreign key as code above?
    Friday, April 9, 2010 2:20 PM

  • Will I need to add a Column to foreign key as code above?

    Yes, all properties that map to DB columns need a column attribute.
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Saturday, April 10, 2010 2:52 AM
    Answerer