Populating SelectList based on three different tables

Question

I'm not so experienced with ASP.Net MVC nor with Entitity Framework.

The problem is the following: I do have database table for tax code sets, organizations and table 'DisplayData' which contains information which sets are linked with which organization. Returning all the organizations and all the tax code sets works very well. But now I should be able to build the list of tax codes based on chosen organization.

Relevant code this far looks like this:

       IEnumerable<TaxCodes> taxcodelist = null;
       IEnumerable<Organizations> orglist = null;
       IEnumerable<DisplayData> taxcodeids = null;

       model.OrgId = Convert.ToInt64(Request.QueryString["OrgId"]);
       if (model.OrgId == 0) 
           model.OrgId = 1;


       taxcodelist = db.TaxCodes.AsNoTracking().Distinct().OrderBy(s => s.Id).ToList();
       orglist = db.Organizations.AsNoTracking().Distinct().OrderBy(s => s.org_id).ToList();
       taxcodeids = db.DisplayData.Where(s=>s.CompanyId == model.OrgId).ToList();

       model.TaxCodeList = new SelectList(taxcodelist, "ID", "Name");
       model.OrgList = new SelectList(orglist, "org_id", "org_name");

Well, this works, but really the problem is how to make my TaxCodeList to include only the options that match with the organization?

Thanks for help in advance!

Actually, no. The whole taxcodeids hangs there with no use. I created it in a first place to try something like creating an array with taxcodeids and looping through and removing non-matching items from TaxCodeList, but couldn't find a way to do that either. — Athene noctua, Jul 11 '13 at 10:07
Oops, I party misunderstood your question! In "DisplayData" table there is column named "ORGANIZATION ID" and "TAX_CODE_ID" where I should be able to find the link between the organization and set. — Athene noctua, Jul 11 '13 at 10:27

score 0 · Accepted Answer · answered Jul 11 '13 at 10:32

try something like this

   taxcodeids = db.DisplayData.Where(s=>s.CompanyId == model.OrgId).ToList();
   taxcodelist = db.TaxCodes.AsNoTracking().Distinct().Where(x=> taxcodeids.Contains(y => y.ID == x.TAX_CODE_ID)).OrderBy(s => s.Id).ToList();
   orglist = db.Organizations.AsNoTracking().Distinct().OrderBy(s => s.org_id).ToList();


   model.TaxCodeList = new SelectList(taxcodelist, "ID", "Name");
   model.OrgList = new SelectList(orglist, "org_id", "org_name");

there are cleaner ways of doing it, will work on that now

from dd in db.DisplayData
join tc in db.TaxCodes on dd.TAX_CODE_ID equals tc.ID
where dd.CompanyId == model.OrgId
select tc

That should do it, bear in mind that i haven't tested that yet tho

Populating SelectList based on three different tables

1 Answers1