How to select distinct data from two tables based on two parameters?-CodePudding

I have two lists. StateNotes - State, CropYear, ..Other properties Pricings - State, CropYear, ..Other properties

I want to select State and CropYear of every record in Pricings table which is NOT present in StateNotes.

Example :

StateNotes

{State - Washington, CropYear - 2021 },
{State - Alabama, CropYear - 2022 },
{State - Nevada, CropYear - 2022 }

Pricings

{State - Washington, CropYear - 2021 },
{State - Alabama, CropYear - 2022 },
{State - Nevada, CropYear - 2021 } *<- this would be selected because there is no combination of Nevada-2021 in StateNotes*

What I tried :

var stateNotes = repository.AsQueryable<StateNotes>().ToList();
var newStates = pricings.Select(x => new { x.State, x.CropYear }).Except(stateNotes.Select(x => new { x.State, x.CropYear })).ToList();

CodePudding user response：

This should work

var newStates = pricings.Where(x => !stateNotes.Any(p => p.State == x.State && p.CropYear == x.CropYear)).ToList();

Whit that function you select the difference between pricings and stateNotes