CODEWITHSUNDEEP
c#linq
Khen
Khen

Reputation: 125

Linq search on array of object

I'm trying to create a linq query that can be able to search through an array of objects. This is what I can do on the SQL

select * from Compliance c
join ComplianceDetail cd on cd.ComplianceID = c.ComplianceID
join ComplianceDetailAnswer cda on cd.ComplianceDetailID = cda.ComplianceDetailID
where cda.ComplianceQuestionValue like '%test%'

As you can see, the heirarchy is one Compliance to many ComplianceDetail and one ComplianceDetail to many ComplianceDetailAnswer. I want to search on the ComplianceDetailAnswer table but I have no idea how to do it in linq.

Here's my original code but I'm searching on the Compliance table itself

compliances = await _contextProvider.Context.Compliances
               .Where(c.IncidentReportID.ToString().Contains(searchText) || searchText == null)
               .ToListAsync();

This is just searching on the Compliance table, but I want to be able to search on its child table ComplianceDetailAnswer

Upvotes: 0

Views: 170

Answers (2)

Dominik
Dominik

Reputation: 1741

Here's another answer using System.Linq. Each IEnumerable<T> supports Take() and Skip().

As long as you do not iterate the result - in my case compliancesWithQuestionsLikeTest the query will not be executed, since it's just an IQueryable object. Adding Take() and Skip() should therefore generate an SQL statement that has something like TAKE FIRST 50 ROWS ONLY and thus give you a performance benefit.

class Compliance
{
    public List<ComplianceDetail> ComplianceDetails { get; set; }
}

class ComplianceDetail
{
    public List<ComplianceDetailAnswer> ComplianceDetailAnswers { get; set; }
}

class ComplianceDetailAnswer
{
    public string Question { get; set; }
}

static void Main(string[] args)
{
    var obj = new Compliance();
    var queryList = new List<Compliance> { obj };

    var compliancesWithQuestionsLikeTest = queryList.Where(compliance => compliance.ComplianceDetails
            .Any(complianceDetail => complianceDetail.ComplianceDetailAnswers
                .Any(answer => answer.Question.Contains("test"))));

}

In this sample queryList basically is your _contextProvider.Context.Compliances.

EDIT:

Please note that this query will return the Compliance objects in an unmodified manner. You will get only Compliances containing a question with test. The Compliance objects however also contain all other question related to them in the database. The objects are not manipulated!

As you can see in Tomas Chabada's answer he creates new objects only containing questions with "test" in select new { c, cd, cda }.

Upvotes: 1

Tomas Chabada
Tomas Chabada

Reputation: 3019

You can compose it in LINQ easily. Here is an example using query syntax:

var result = from c in Compliance
        join cd in ComplianceDetail on c.ComplianceID equals cd.ComplianceID
        join cda in ComplianceDetailAnswer on cd.ComplianceDetailID equals cda.ComplianceDetailID
        where cda.ComplianceQuestionValue.Contains("test")
select new { c, cd, cda };

If you need to call Skip and Take simply wrap your expression to parentheses and the you can chain other LINQ methods

var result = (from c in Compliance
              join cd in ComplianceDetail on c.ComplianceID equals cd.ComplianceID
              join cda in ComplianceDetailAnswer on cd.ComplianceDetailID equals cda.ComplianceDetailID
              where cda.ComplianceQuestionValue.Contains("test")
              select new { c, cd, cda }).Skip(1).Take(100);

Upvotes: 1

Related Questions