MVC / EF Query -- A better way?

Objective: Get a list of name|id pairs for a select list from table A where the id does not exist in a filtered collection of Table B records.

Constraints: Using MVC 1.0, Entity Framework (no L2S, DataContext, etc.), C#

My question is whether there's a smarter way to go about doing this than what I've got working so far--which just seems incredibly obtuse for such a simple need. Here's what I've got:

Database:

Agencies ( AgencyId (int) (PK), AgencyName (varchar), Active (bit) ...) AgenciesDonors (AgenciesDonorId (int) (PK), AgencyId (int) (FK), DonorId (Guid) (FK), ...)

Restating the Objective: Build a SelectList object that contains all the AgencyNames that have NOT been assigned to a Donor as indicated by the AgencyId value appearing in 1 or more AgenciesDonors records that have a common Donor Guid.

Here's one solution that works. In AgencyRepository, this:

//Returns all Agencies not assigned (in AgenciesDonors) to the donor as a SelectList
public SelectList GetAvailableAgencyList(Guid donorId, Int32 selected)
{
    string[] assigned = db.AgenciesDonorSet.Include("Donors").Include("Agencies").Where(ad => ad.Donors.DonorId == donorId ).Select(x => x.Agencies.AgencyName).ToArray();
    string aIdS = string.Join("','", assigned.ToArray());
    var selectable = db.AgencySet.Where("it.AgencyName not in {'" + aIdS + "'} and it.Active");
    return(new SelectList(selectable, "AgencyId", "AgencyName", selected));
}

In the controller, this:

        SelectList agencyList = repo.GetAvailableAgencyList(new Guid(Request["DonorId"]), 0);

In the view, this:

<%=Html.DropDownList("AgencyList", (IEnumerable<SelectListItem>)ViewData["AgencyList"])%>

ansaurus

tags:

views:

answers:

MVC / EF Query -- A better way?

related questions