LINQ connecting Many - Many relationships

Answer 1

You've not joined any of the other tables, hence the results are only bringing back the tblAvailabilities rows.

var query = (from avail in dc.tblAvailabilitities
        join aaTyp in dc.tblAvailabilityAppointmentType
             on avail.availabilityID == aaType.availabilityID
        join appType in dc.tblAppointmentType
             on aaType.appTypeID == appType.appTypeID
        select new { TypeName = appType.appTypeName });

Answer 2

With your current schema, you would possibly have multiple records for tblAvailabilityAppointmentType per record in tblAvailabilities. That means you need to get a specific entry before trying to tie into tblAppointmentType. Also, you are not joining in the other tables to retrieve the rows (if you want to do it that way).

As an aside, is many to many really what you want here? Your usage (as well as the concepts I infer from your naming) makes it seem like you want a one to many relationship. Perhaps many tblAvailabiliy records, each referencing one tblAppointmentType?

If you do want many to many, you may be well served extending the tblAvailabilities class with a GetAppointmentTypes() method. Here's a decent article on codeproject.

Othewise, make sure you join in the other tables and reference the right row in tblAppointmentType

Answer 3

LINQ2Sql doesn't handle Many-2-Many well without a little hand-holding...

remove the link between tblAvailabiltyAppointmentType and tblAppoitmentType. Make a new link FROM tblAvailabiltyAppointmentType TO tblAppoitmentType. click on that new link to get to the properties. change the Cardinality to OneToOne (since you can't pick ManyToOne), open up the Cild Property section, and change the name to singular.

now, when you perform the query you have in the question, the tblAvailability object will have a collection tblAvailabilityAppointmentTypes, and each tblAvailabilityAppointmentType object in that collection will have a property object tblAppointmentType, which has the appTypeName field you're looking for.

var appTypes = GetAvailabilitiesBySet(99).tblAvailabilityAppointmentTypes.
    Select(a=>a.appTypeName);

Answer 4

I added another repeater inside the first repeater with accessed the correct database table.