As many other times, I came across this issue having to begin at the wrong end, i.e. having the result and in need of the problem. The samples and recommended procedures offer – as in most cases – only the simplest examples. The workaround is often tedious and in absence of feedback uncertain. This is why suggestions and comments are more than welcome.
Having already done my data model including validation attributes and documentation, I wanted to use LINQ for the data access layer . The tables in the database were created by scripting after the data model was ready. The procedures I have found so far generate this the other way around, starting from the database structure and generating objects and dependencies.
As I did not want to do the same work again, here is an example of a class (Leg) , its equivalent in the database (TranspLeg) with double reference, first to parent table "Transport" (foreign key for Transport table) and then to child table "TranspSeatClass" (the TranspSeatClass table that holds the foreign key to the TranspLeg table).
The parent class needs to have an EntitySet property and the child class an EntityRef property. In this way I am able to read the whole object from the database with its dependents and also to save/insert an object in the database in all dependent tables.
After the example there are some issues I came across.
[Table(Name = "TranspLeg")] [Serializable] public class Leg { #region LINQ private EntityRef _Transport; /// /// reference to the Transport class /// public Leg() { this._Transport = default(EntityRef); this._TranspSeatClass = new EntitySet(new Action(this.attach_TranspSeatClass), new Action(this.detach_TranspSeatClass)); } private void attach_TranspSeatClass(TranspSeatClass entity) { //this.SendPropertyChanging(); entity.Leg = this; } private void detach_TranspSeatClass(TranspSeatClass entity) { //this.SendPropertyChanging(); entity.Leg = null; } /// /// FK for transport /// [Association(Name = "FK_TranspLeg_TranspId", Storage = "_Transport", ThisKey = "TranspId", IsForeignKey = true)] public Transport Transport { get { return this._Transport.Entity;} set{ Transport previousValue = this._Transport.Entity; if (((previousValue != value)|| (this._Transport.HasLoadedOrAssignedValue == false))) { this._Transport.Entity = value; if ((value != null)) { this._TranspId = value.ID;} else{ this._TranspId = default(int);} } } } private EntitySet _TranspSeatClass; /// /// set ref to child table /// [Association(Name = "FK_SeatClass_LegId", Storage = "_TranspSeatClass", OtherKey = "LegId", DeleteRule = "NO ACTION")] public EntitySet TranspSeatClass { get { return this._TranspSeatClass; } set { this._TranspSeatClass.Assign(value); foreach (TranspSeatClass t in this.SeatClassList) { t.Leg = this; } } } #endregion private int _id; /// /// ID for the class instance /// [Column(Name = "ID", DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)] public int ID { get { return _id; } set { _id = value; } } private int _TranspId; /// /// Transport ID for the class reference /// [Column(Name = "TranspId", DbType = "Int NOT NULL", CanBeNull = false)] public int TranspId { get { return _TranspId; } set { _TranspId = value; } } } /// /// seat class list for leg /// [Table(Name = "TranspSeatClass")] [Serializable] public class TranspSeatClass { #region LINQ private EntityRef _Leg; /// /// /// public TranspSeatClass() { this._Leg = default(EntityRef); } /// /// FK for leg /// [Association(Name = "FK_SeatClass_LegId", Storage = "_Leg", ThisKey = "LegId", IsForeignKey = true)] public Leg Leg { get { return this._Leg.Entity; } set { Leg previousValue = this._Leg.Entity; if (((previousValue != value) || (this._Leg.HasLoadedOrAssignedValue == false))) { this._Leg.Entity = value; if ((value != null)) { this._LegId = value.ID; } else { this._LegId = default(int); } } } } #endregion }
You might consider the following:
1. the attributes for the identity column must be set as in the database:
[Column(Name = "ID", DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
especially the "IsDbGenerated = true" is important.
2. if you have DateTime properties you might need to set them nullable as the SQL minimum date is different from the .NET minimum date for whatever reason. So initialization ofDateTime properties and SQL columns have different values and an error message is thrown.
3. in order to use the above classes and mappings you need a data access class derived from DataContext. For each table you are using you will need a table declaration as below :
public partial class MyDataContext : DataContext { AttributeMappingSource(); public MyDataContext(string connection) : base(connection, mappingSource) { } public System.Data.Linq.Table TranspLeg { get { return this.GetTable(); } } }
to insert the object into the database :
MyNS.DAL.MyDataContext db = new MyNS.DAL.MyDataContext("myconnectionstring"); db.TableToInsert.InsertOnSubmit(myObj); db.SubmitChanges();
to retrieve tables from DB :
Table LegsTable = db.GetTable();
Check for updates on this post as I’m progressing through project phases.
