CODEWITHSUNDEEP
linqentity-frameworkef-code-first
Benk
Benk

Reputation: 1312

Contains method for comparing in multiple table

I am using EF code first. Two tables.

Company table (field: CompanyName) and Tag table (TagName). Company table has a link to the Tag Table

    public class Company   
    {
     public int Id { get; set; }
     public string CompanyName { get; set; }
     public virtual ICollection<Tags> Tags { get; set; }
    }

I want to retrieve all companies where CompanyName contains string that is been passed and also retrieve all companies that have that string as a tag

        var result = from c in _db.Company
                     select new CompaniesVM  
                     {
                         Id = c.Id,
                         CompanyName = c.CompanyName
                     };

        if (!String.IsNullOrEmpty(searchString))
        {
            result = result.Where(s => s.CompanyName.Contains(searchString));
        }

My database contains:

Company name: test1

Company name: company test

if I run the code with searchString "test" it only captures the first record. Contains method does not capture the second record. Why is that? How do I capture all records with "test" string?

Also if I have TagName "test"

Company name: ABC Inc. Tag: TagName : test

How do I look into Tags table as well and get the companies?

I am using Many-to-Many example schema below

enter image description here

Can I do it all in one query? maybe using lazy loading method?

much appreciated if you can point me to a similar example or sample code

Upvotes: 0

Views: 342

Answers (1)

artm
artm

Reputation: 8584

Contains is case sensitive so if your data is uppercase and your searchTerm is lower case, they won't match. If you want to ignore case when searching use result.Where(s => s.CompanyName.ToLower().Contains(searchString.ToLower())). To search the Tag table as well as Company table join them

from c in _db.Company
join t in _db.Tag on c.field equals t.field
select new { Companyname = c.CompanyName, TagName = t.TagName}
where !String.IsNullOrEmpty(search) && (c.CompanyName.Contains(search) || t.TagName.COntains(search)

Update for many to many, since you have the collection on Company:

from c in _db.Company
select new {
     CompanyName = c.CompanyName
}
where !string.IsNullOrEmpty(search) 
      && (c.CompanyName.Contains(search) || c.Tags.Any(t => t.TagName.Contains(search))

Upvotes: 2

Related Questions