asp.net mvc Linq exclude values from other table

Question

I have an exhibition scenario.

How do I exclude a list of available Stands below, from the drop down list I would show in the Exhibitor view.

In Sql I would:

Select StandID, Description from Stand 
Where StandID not in (Select StandID from Exhibitor)

Exhibitors register - and select the stand they want from a dropdown list (StandID/Stand) as per the model below:

 public class Exhibitor
{
    public int ExhibitorID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public int StandID { get; set; }
    public virtual Stand Stand { get; set; }
 }

The Stand Model is as follows:

  public class Stand
{
    public int StandID { get; set; }
    public string Description { get; set; }
    public bool Booked { get; set; }
}

I can get a list of Stands as follows:

var stands = db.Stands.ToList().Where(s => s.Booked==false)
          .Select(s => new SelectListItem
          {
              Value = s.StandID.ToString(),
              Text = s.StandNumber + ": " + s.Description + ": " + s.Size + ": £" + s.Rate.ToString()
          });

But how do I exclude the (Select StandID from Exhibitor) from the stands list?

I tried:

var booked = db.Exhibitors.Select(s => new { s.StandID }).ToList();

then using:

var stands = db.Stands.ToList().Where(s => s.Booked==false)
          .Except(booked)
          .Select(s => new SelectListItem
          {
              Value = s.StandID.ToString(),
              Text = s.StandNumber + ": " + s.Description + ": " + s.Size + ": £" + s.Rate.ToString()
          });

But that didn't work.

UPDATE

This appears to work - but I'm no expert, so would appreciate any advise on if it;s the best practice way or not?

var stands = db.Stands.ToList().Where(s => !db.Exhibitors
.Any(bk => bk.StandID == s.StandID) && s.Booked == false)
          .Select(s => new SelectListItem
          {
              Value = s.StandID.ToString(),
              Text = s.StandNumber + ": " + s.Description + ": " + s.Size + ": £" + s.Rate.ToString()
          });

Thanks for any help?

Mark

BZink · Accepted Answer · 2012-10-05 01:25:18Z

3

Use the Linq Contains() method

http://msdn.microsoft.com/en-us/library/system.linq.enumerable.contains.aspx

var booked = db.Exhibitors.Select(s => s.StandID).ToList();
var stand = db.Stands.Where(s=>s.Booked == false 
                                       && !booked.Contains(s.StandId)
              .Select(s => new SelectListItem
              {
                  Value = s.StandID.ToString(),
                  Text = s.StandNumber + ": " + s.Description + ": " + s.Size + ": £" + s.Rate.ToString()
              });

I see you updated your question about best practices, too. There's a lot of optimization that can be done to your technique. First, read about the differences between IEnumerable and IQueryable.

Specifically speaking, you should remove the ToList() from Stands.ToList().Where(). By performing ToList() before Where() you're bringing back all of your rows from the db, and then processing them in memory. This could be very costly. If you perform Stands.Where(...) then your filtering will be done in sql and only the rows you want will be returned.

Generally, save your ToList() to be your final step, and actively decide if you need the list to be constructed at all.

edited Oct 5, 2012 at 1:25

answered Oct 4, 2012 at 20:28

BZink

7,99110 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Over a year ago

Hi - I received the error: Error The best overloaded method match for 'System.Collections.Generic.List<AnonymousType#1>.Contains(AnonymousType#1)' has some invalid arguments

BZink Over a year ago

Updated my answer. You don't need to create an anonymous object for your "booked" list. You need a List<int>. (I just removed your new statement)

Shawn Gavett Over a year ago

This helped me figure out my own solution. Thank you @BZink

Pooran · Accepted Answer · 2015-03-14 18:06:34Z

0

Ideal way is always to create a model that you want as output, map the output to that model and return it. This will also help the client side to get a definitive model to stick to and implement.

Also complex select queries in LINQ are generated on the fly which takes more time than a well structured stored procedure, which is pre-compiled. Consider this if you are expecting faster responses.

answered Mar 14, 2015 at 18:06

Pooran

1,6902 gold badges19 silver badges27 bronze badges

Collectives™ on Stack Overflow

asp.net mvc Linq exclude values from other table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related