CODEWITHSUNDEEP
asp.netasp.net-mvc-3linqentity-framework
Greg B
Greg B

Reputation: 813

LINQ: With Many to many relationship, select from list

I've having trouble figuring out how to select based on a list in a many to many relationship.

I've created with entities-framework the following entities and many to many relationship (please correct me if I'm going about this wrong) :

public class Foo {
  public int FooID { get; set; }
  public string FooName { get; set; }
  public virtual ICollection<FooBar> Foo_Bar { get; set; }
}

public class Bar {
  public int BarID { get; set; }
  public string BarName { get; set; }
  public virtual ICollection<FooBar> Foo_Bar { get; set; }
}

public class FooBar {
  public int FooBarID{ get; set; }
  public virtual int BarID { get; set; }
  public virtual int FooID { get; set; }
}

In my code my controller will receive a list of Foo and I need to find all the Bar with those foo ( both with only and with any )

I'm at a loss for where to start really... this is all I've come up with:

public PartialViewResult SearchAnyBar(List<Foo> foos) {
    List<FooBar> foobars = _db.FooBar.Select(fb => fb).ToList<FooBar>();
    List<Bar> searchedBars = new List<Bar>();

    foreach (Foo f in foos)
    {
        foreach (FooBar fXb in foobars) 
        {
            if (fXb.FooID == f.FooID)
            {
                searchedBars.Add(_db.Bar.Where(b => b.BarID == fXb.BarID).FirstOrDefault());
            }
        }
    }        

    return PartialView("The View", searchBars);
}

This works for the grabbing any Bar however:

  1. I'm pretty positive there's a much better way of doing this, is there a way to select based on a list instead of going about 2 foreach loop?

  2. I'm not sure how to go about getting a list of Foos where the Foo has ALL the Bars and not just ANY.

Upvotes: 3

Views: 3218

Answers (2)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

Remove FooBar class.

Just create a public virtual ICollection<Foo> Foos {get;set;} in you Bar class

and a public virtual ICollection<Bar> Bars {get;set;} in your Foo Class

This will create a many to many relationship (with a relation table named [Foo-Bar] or something like that in your db... but who minds, you will be using objects).

then

any query :

var listOfFooId = <a list of Foo>.Select(m => m.FooId).ToList;
return _db.Bar.Where(m => m.Foos.Any(x => listOfFooId.Contains(x.FooId)));

not sure I understood well the "only" and "any", but if you have problems with the other query... ask.

Upvotes: 7

Ryan Bennett
Ryan Bennett

Reputation: 3432

Untested, but it looks like you just need a join here... Joining on all the Bars off of all the FooBars joined to the Foos passed in, right? 

public PartialViewResult SearchAnyBar(List<Foo> foos) { 

    var bars = (from f in foos
               join fb in _db.FooBars on f.Id equals fb.FooId
               join b in _db.Bars on fb.BarId equals b.BarId
               select b).ToList();



        return PartialView("The View", bars); 
    }

Upvotes: 0

Related Questions