CODEWITHSUNDEEP
.netasp.net-mvc-3linqentity-frameworklambda
MayorAwesome
MayorAwesome

Reputation: 183

LINQ query help: searching for data in a Many to Many using Entity Framework

I've poked around and seen a bunch of similar questions, but I still can't figure this out. I've got two classes Assets and Tags. Here are their definitions:

public class Asset
{
    [Key]
    public int AssetID { get; set; }

    public decimal? Height { get; set; }
    public decimal? Width { get; set; }
    public decimal? Depth { get; set; }
    public decimal? Radius { get; set; }
    public string Name { get; set; }

    [Display(Name = "Manufacturer")]
    public int ManufacturerID { get; set; }

    [Display(Name = "Type")]
    public int PackageTypeID { get; set; }

    public virtual Manufacturer Manufacturer { get; set; }
    public virtual PackageType PackageType { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }

}

public class Tag
{
    public int TagID { get; set; }
    public string Key { get; set; }
    public string Value { get; set; }
    public virtual ICollection<Asset> Assets { get; set; }

}

I've got a search box on my Asset Index page and I want to be able to search for Asset.Name, Manufacturer.Name, Asset.Tag.Key, and Asset.Tag.Value. Here's what I've got so far. 

var assets = from a in db.Assets                        
                     select a;
if (!String.IsNullOrEmpty(searchString))
        {
assets = assets.Where(a => a.Name.ToUpper().Contains(searchString.ToUpper())
          || a.Manufacturer.Name.ToUpper().Contains(searchString.ToUpper()));
}

Now, I have no idea how the heck to access/search for The Tag.Key or Tag.Value. I've tried all sorts of joins with the LINQ query, but I'm just not smart enough.

Any help would be appreciated.

Upvotes: 2

Views: 135

Answers (3)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

You can use Any to check the Tags and filter by searchString

var result = db.Assets.Where(a => a.Name.ToUpper().Contains(searchString.ToUpper()) ||
                                  a.Manufacturer.Name.ToUpper().Contains(searchString.ToUpper()) ||                       
                                  a.Tags.Any(t => t.Key.ToUpper().Contains(searchString.ToUpper()) || t.Value.ToUpper().Contains(searchString.ToUpper()));

Upvotes: 2

lante
lante

Reputation: 7336

First of all, if you want to make a search "case insensitive", you change the collation of your database table columns.

Then, your LINQ code:

var assets = db.Assets.Where(a => a.Name.Contains(searchString)
      || a.Manufacturer.Name.Contains(searchString)
      || a.Tags.Any(t => t.Key == searchString || t.Value == searchString));

Upvotes: 1

NinjaNye
NinjaNye

Reputation: 7126

Try using the .Any() extension method to find if the asset contains a tag with the defined search term

var result = db.Assets.Where(a => a.Name.ToUpper().Contains(searchString.ToUpper())
                               || a.Manufacturer.Name.ToUpper().Contains(searchString.ToUpper()
                               || a.Tags.Any(t => t.Key == searchString)
                               || a.Tags.Any(t => t.Value == searchString)

Upvotes: 0

Related Questions