CODEWITHSUNDEEP
sql-serverdatabaselinq
streamc
streamc

Reputation: 698

How to remove bool flag from linq query?

I have Acts table entity and Collection

public partial class Act
{
    public virtual ActCondition ActCondition { get; set; }
    public virtual ICollection<Repair> Repair { get; set; }
}

public partial class Repair
{
    public virtual ICollection<RepairedDetails > RepairedDetails { get; set; }
}

public partial class RepairedDetails 
{
    public virtual Repair Repair { get; set; }
}

It have Actcondition table with primary key ActID Repair table linked with Act and RepairedDetails table linked with Repair table How can I simplify and optimize this query

List<Act> acts = new List<Act>();
foreach (var item in db.Act.ToList())
{
    bool repflag = false;
    if (item.Repair != null &&
        item.ActCondition.ProcessedStorage == false &&
        item.ActCondition.ProcessedBookkeeping == false
        )
        foreach (var r_item in item.Repair)
            if (r_item.RepairedDetails != null)
                repflag = true;
    if (repflag == true) acts.Add(item);
}

How can I optimize that query?

Upvotes: 1

Views: 95

Answers (2)

gotqn
gotqn

Reputation: 43646

Try this:

foreach (var item in db.Act.ToList())
{
    if (item.ActCondition.ProcessedStorage == false && item.ActCondition.ProcessedBookkeeping == false && item.ActCondition.Completed == false && item.ActCondition.ProductSent == false&& item.Repair.Any(nx => nx.RepairDetails.Count>0) ) 
    acts.Add(item);   
}

Upvotes: 1

Alexandr S
Alexandr S

Reputation: 309

Do you use Lazy loading? It can have a bad influence on performance then it uses with foreach Instead of db.Acts.ToList(), you should use db.Acts because.ToList() finalize the query and cast collection from IQueryable to IEnumerable.

You can use Eager Loading to manually join linked Entities. I don't know what version of EF you use so that example for EF6.

var query = db.Act
    .Include(p => p.ActCondition)
    .Include(p => p.Repair)
    .ThenInclude(p => p.RepairedDetails).AsQueryable();

query = query.Where(item => item.Repair != null &&
        item.ActCondition.ProcessedStorage == false &&
        item.ActCondition.ProcessedBookkeeping == false && p.Repair.RepairedDetails.Any())

List<Act> acts = query.ToList();

Hope it helps.

Upvotes: 2

Related Questions