is is possible to have more than 1 table in the xxx.dbml file?

Question

I am trying to put more than 1 table in my Appointement.dbml.

I drag and drop the Appointement table and the doctor.

Table Appointement is define as aptId, patientId, doctorId, aptDate, HrDate, IsAvailable. Table Doctor is define as Id, doctorName.

I need both table to be able to display the doctor name in the Listview to choose and appointement.

The problem I got is in the Appointement.designer.cs I got many errors.

Ex.:

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Doctor")]
public partial class Doctor : INotifyPropertyChanging, INotifyPropertyChanged
{

private static PropertyChangingEventArgs emptyChangingEventArgs = new 
    PropertyChangingEventArgs(String.Empty);

private int _Id;

private string _Doctor_name;  <=== Error The type 'Doctor' already contains a definition for 
                                        '_Doctor_name'

I search for _Doctor_name, here are the results:

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Doctor_name", 
    DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string Doctor_name
{
    get
    {
        return this._Doctor_name;
    }
    set
    {
        if ((this._Doctor_name != value))
        {
            this.OnDoctor_nameChanging(value);
            this.SendPropertyChanging();
            this._Doctor_name = value;
            this.SendPropertyChanged("Doctor_name");
            this.OnDoctor_nameChanged();
        }
    }
}

Can someone explain me what is wrong with the Appointement.designer.cs file. Did I miss one step ? I don't understand the error The type 'Doctor' already contains a definition for '_Doctor_name'

Thanks for your help

Should it be here we made some changes to joint the two table instead of in the xxx.designer.cs?

 <asp:LinqDataSource ID="LinqDataSource1" runat="server" 
    ContextTypeName="AppointementDataContext" 
    EnableDelete="True" EnableInsert="True" EnableUpdate="True" EntityTypeName="" 
    TableName="Appointement" 
        Where="IsAvailable = True && dateApt >= DateTime.Now && doctorId = @doctorId"> 

            <WhereParameters>
               <asp:ControlParameter 
                   Name="doctorsId" 
                   ControlID="DropDownList1" 
                   PropertyName="SelectedValue"

                   Type="String" />
             </WhereParameters>

        </asp:LinqDataSource>

Here is the code in the AppointementDoctor.aspx.cs

private void ListAppointement(int iIndexDdl)
{
    using (AppointementDataContext db = new AppointementDataContext())
    {
        var rdvItems = from Appointement in db.Appointement
                       where Appointement.doctorId == iIndexDdl && Appointement.isAvailable == 
        true && Appointement.dateApt >= DateTime.Now 
                       select Appointement;


                  ListView1.DataSourceID = null;
                  ListView1.DataSource = rdvItems;

                  int numberOfRecords = rdvItems.Count();
                  if (numberOfRecords == 0)
                  {
                      lblMessage.Text = "No Appointement is available";
                  }

                  ListView1.DataBind();
    };
}

Answer 1

Have the Doctor table in your DBML be exactly like the doctor table in your database and teh AppointmentTable exactly the same as in your database too.

Now in your code add a class which will act as your View Model for your UI, call it say AppointmentDoctorViewModel..

public class AppointmentDoctorViewModel
{ 
    public int DoctorId {get;set;}
    public string DoctorName {get;set;}
    public string Subject{get;set;}
    // etc, other fields for your ListView
}

Now, in a Data Layer, Repository class or if need be in your .aspx, write a linq query that builds up the class like this:

var viewData = from appointment in AppointementDataContext
    select new AppointmentDoctorViewModel
    {
        DoctorId = appointment.Doctor.Id,
        DoctorName = appointment.Doctor.Name,
        Subject = appointment.Subject
    };

The use an ObjectDataSource to bind against the ListView in your apx.

This gives you a lot more control and nicely separates the data layer from the Domain Model you're working with.

Read up on:

• MVVM
• Using the ObjectDataSource with Entity Framework (the pattern will be similar for Linq-2-SQL if that's what you're using.