Query a Many to Many to show certain Events

Question

I have an application for Volunteers to apply for Ceremonies.

They look up a list of ceremonies that are available and they simply click join and the relationship is created.

However, I would like this ceremony to be removed from the list if they are part of it.

Here's my action in my controller:

 public ActionResult ViewAvailableCeremonies()
    {
        string username = Membership.GetUser().UserName;

        var getVolunteer = (from vol in db.Volunteers
                            where username == vol.Username
                            select vol).SingleOrDefault();


        var ceremonies = (from a in getVolunteer.Appointments
                          where a.Fee != null
                          && a.Slots != 0
                          //&& getVolunteer.Appointments.Any(c => a.)
                          select a).ToList();



        return View(ceremonies);
    }

I've been trying to think of how to go about this but my brain is totally fried. I know I need to query the list to check if they are there.

As always, thanks for the help

Answer 1

Looks like you are filtering your available cerimonies based on Users appointments.

First you have to search for all the cerimonies:

E.G.

var ceremonies = (from a in db.Cerimonies
                  select a).ToList();

So, you filter the cerimonies, based on users Appointments:

I'm assuming that you have an relationship between Ceremony and Appointment where the foreign key in Appointment class is CerimonyID and the primary key in Ceremony class is ID

E.G.

string username = Membership.GetUser().UserName;

var getVolunteer = (from vol in db.Volunteers
                    where username == vol.Username
                    select vol).SingleOrDefault();

var userAppointmentsIds = (from a in getVolunteer.Appointments
                            where a.Fee != null &&
                            a.Slots != 0
                            select a.CerimonyID).ToList();

var filteredCerimonies = ceremonies
                         .Where(c => !userAppointmentsIds.Contain(c.ID))
                         .ToList();

Answer 2

So in you application you have a Volunteer, hopefully identified by his unique Id, or if really necessarily by non-unique Username.

In this application you want to show a list of all Ceremonies that this Volunteer is not attending yet.

In your example code I see something quite different. I see Appointments (is an Appointment the same as a Ceremony?) Apparently there is a requirement not to use all Appointments, but something that have non-null Fees and non-zero Slots,

Leaving that apart I see that you access your database (or your tables) more than once, which is not really efficient.

For efficiency reasons: try to keep your LINQ statements IEnumerable / IQueryable as long as possible, only when you need the final result use ToList / ToDictionary / SingleOrDefault / FirstOrDefault / Any etc

Concatenation of LINQ statements is fast. It just changes the expression needed to enumerate the sequence. The slow part is when you start enumeration in your ToList / Any / etc.

Having said this. I'm not sure if you're using entity framework to access your database. If so, your answer is easy. See the end.

Solution without entity framework

So to identify your Volunteer you have a unique volunteerId, or if not available a (fairly) unique userName.

You also have a sequence of Volunteers and a sequence of Ceremonies (which are Appointments with non-null Fee and non-zero Slots.

There is a many-to-many relationship between Volunteers and Appointments: Every Volunteer can have zero or more Appointments; every Appointment is attended by zero or more Volunteers.

In databases this is implemented using a junction table, let's call this table VolunteersAppointments.

Okay, so we have properly defined your problem. Here are the classes:

class Volunteer
{
    public int Id {get; set;}
    public string UserName {get; set;}
    ...
}
class Appointment
{
    public int Id {get; set}
    public <someType1> Fee {get; set;}
    public <someType2> Slots {get; set;}
    ...
}
// junction table
class VolunteersAppointments
{
    public int UserId {get; set;}
    public int AppointmentId {get; set;}
}

It might be that you've defined your classes differently, but you get the gist.

To get all Ceremonies the user with UserName is not attending yet:

string userName = ...
IQueryable<Volunteer> volunteers = <your table of all Volunteers>
IQueryable<Appointment> appointments = <your table of all appointments>

// ceremonies are appointments with non-null Fee and non-zero Slots:
var ceremonies = appointments
    .Where(appointment => appointment.Fee != null && appointment.Slots != 0);

var volunteersWithUserName = volunteers
    .Where (volunteer => volunteer.UserName == userName);
    // if Volunteer.UserName unique, this will lead to a sequence with one element
    // if not unique, consider selection by Id

Now to get all ceremonies that are not attended by this volunteer I first need to get the ceremonies that are attended by the volunteer. For this I need the junction table. Join Volunteers with junction table with Ceremonies.

Normally I would use LINQ method-syntax instead of LINQ query-syntax, because it has more functionality. However a join with three tables looks horrible in method syntax. For this I use query syntax:

var attendedCeremonies = from volunteer in volunteersWithUserName
    join volap in VolunteersAppointments on volap.VolunteerId equals volunteer.Id
    join ceremony in ceremonies on volap.CeremonyId equals ceremony.Id
    select ceremony;

var notAttendedCeremonies = ceremonties.Except(attendedCeremonies);

In your application I assume that you don't need all properties of the Ceremony. You'll probably only want to show the name / description / Time / Location. You'll also need the Id to make a new Appointment.

var displayableCeremonies = notAttendedCeremonies.Select(ceremony => new
{
    Id = ceremony.Id,
    Name = ceremony.Name,
    Description = ceremony.Description,
    Location = ceremony.Location,
    Time = ceremony.Time,
    Duration = ceremony.Duration,
});

Note that, although I created a lot of separate LINQ queries, no query is executed yet, your tables are not accessed yet. The statements only changed the expression in the query. There is no big performance penalty when writing it like this instead of one big LINQ statement. The bonus is that it is readable and thus better testable and maintainable.

The following will finally do the query:

var desiredResult = displayableCeremonies.ToList();

Entity Framework method

When using entity framework you don't need to perform the joins yourself. If you use the ICollections, entity framework will understand that a triple table join is needed.

class Volunteer
{
    public int Id {get; set;}

    // every volunteer has zero or more Appointments (many-to-many)
    public virtual ICollection<Appointment> Appointments {get; set;}

    ...
}
class Appointment
{
    public int Id {get; set}
    // every Appointment is attended by zero or more Volunteers (many-to-many)
    public virtual ICollection<Volunteer> Volunteers {get; set;}
    ...
}
public MyDbContext : DbContext
{
    public DbSet<Volunteer> Volunteers {get; set;}
    public DbSet<Appointment> Appointments {get; set;}
}

Because I stuck to the Entity Framework Code First Naming Conventions, this is enough for entity framework to understand that I meant to design a many-to-many relationship. Entity Framework will create and maintain the junction table for me. I don't need this table to perform my queries.

using (var dbContext = new MyDbContext())
{
    var ceremoniesNotAttendedByUser = dbContext.Appointments
        // keep only the Ceremony appointments
        // that are not attended by any Volunteer with UserName
        .Where(appointment => 
            // only ceremonies:
            appointment.Fee != null && appointment.Slots != 0
            // and not attended by any volunteer that has userName
            // = sequence of UserNames of the volunteers that attend this appointment
            //   does not contain userName
            && !appointment.Volunteers.Select(volunteer => volunteer.UserName)
                   .Contains(userName))
        // Fetch only the properties you want to display:
        .Select(ceremony=> new
        {
            Id = ceremony.Id,
            Name = ceremony.Name,
            Description = ceremony.Description,
            Location = ceremony.Location,
            Time = ceremony.Time,
            Duration = ceremony.Duration,
        })
        .ToList();
}

You see that I don't need the junction table. Entity Framework makes it thus more natural for me (An appointment is attended by zero or more volunteers) that I dared to show it to you in one statement.