How to exclude from query's result in linq?-CodePudding

In the first picture I have the result of first query, the highlighted part indicates the rows that would be excluded by applying the filter on the second query, in the second I have the result of query select * from exlusion_table

I have to make a change to the first query to have it exclude the items retrieved from the second query

the first query:

var u = from a in cx.VW_LIST
        where (a.PRJ == codPrj) && (a.DATE > date_ || a.DATE == null || date_ == null)
        && (x.Contains(a.CODE) || x.Count() == 0)
        select a)

the second query:

var y = from esc in cx.EXCLUSION select esc

The first query should be modified to exclude all the rows that have the value fcode = the fcode of the second query (in the case in which the fscode of the second query = null) or that (fcode = fcode of the second query && fscode = fscode of the second query )

CodePudding user response：

You can use Any(). ie:

var u = from a in cx.VW_LIST
        where (a.PRJ == codPrj) 
           && (a.DATE > date_ || a.DATE == null || date_ == null)
           && (x.Contains(a.CODE) || x.Count() == 0)
           && (!cx.EXCLUSION.Any( y => x.fscode == y.fscode && x.fcode == y.fcode ))
        select a)

CodePudding user response：

I would simplify this query using fluent notation, query notation in this case can be harder to read:

var excluded = cx.EXCLUSION.AsQueryable();

var query = cx.VW_LIST.Where(vw => vw.PRJ == codPrj)
                      .Where(vw => vw.DATE == null || date_ == null || vw.DATE > date_)
                      .Where(vw => !x.Any() || x.Contains(vw.CODE))
                      // Now exclude the results that match the sub-query
                      .Where(vw => !excluded.Any(esc => vw.fcode == esc.fcode 
                                   && (esc.fscode == null || vw.fscode == esc.fscode)));

var results = query.ToList();

The tricky element is the null for fscode in the excluded table, that needs to act as wildcard match, or negate the fscode comparison.

It is not necessary to split the excluded query out into it's own query, we could have referenced to the cx.EXCLUSION table directly and it would have exactly the same effect, this shows you an encapsulation technique for building the LINQ query in a way that you could easily increase the complexity of the exclusion lookup without creating a mess of your overall query.

You may also find need to conditionally build the query, this is where fluent syntax maintains consistency:

bool filterExcludedRecords = true;
...
var excluded = cx.EXCLUSION.AsQueryable();

var query = cx.VW_LIST.Where(vw => vw.PRJ == codPrj)
                      .Where(vw => vw.DATE == null || date_ == null || vw.DATE > date_)
                      .Where(vw => !x.Any() || x.Contains(vw.CODE));
if(filterExcludedRecords)
    query = query.Where(vw => !excluded.Any(esc => vw.fcode == esc.fcode 
                              && (esc.fscode == null || vw.fscode == esc.fscode)));

var results = query.ToList();