LINQ query operator for log table

Question

I am trying to find the corerct LINQ to SQL query operator and predicate combo that can operate on an audit table.

Imagine a table called Setting that has three columns : rowID, DefID, and Value.

I want to be able to check that every DefID ( in this case all definition 1 through 3 ) has at least one row which has a value set to true.

the LINQ expression should return a bool true or false. For example,

RowID    DefID    Value
1     1        true
2     2        false
3        3        true

LINQ returns false because DefID = 2 does not have any value = true

RowID    DefID    Value
1     1        true
2     2        false
3        2        true

returns false because defid 3 is missing

RowID    DefID    Value
1     1        true
2     1        false
3        2        true
4        3        true

returns true because ALL definitions have at least one value = true

Answer 1

I've never used linq to sql, but the linq to objects would look something like this:

defIds.All(d => d.rows.Any( row => row.Value == true ) )

To do it in raw SQL, I don't think it's possible in one query. You could do something like this though:

select id from DefIds
join rows on row.DefId = DefIds.ID
where row.Value = true

That would give you a list of defId's which have true values. In code you could then do something like

DefIds.Select(d => d.id).ToArray() == (results from select).ToArray()

Answer 2

Well, there are a lot of valid ways to do this. One simple way is:

int[] DefIDs = new int[] {1, 2, 3};

bool bHasValidDefs =
    (
    from set in myDataBase.settings
    where 
     set.Value == true
     && DefIDs.Contains(set.DefID)
    select set.DefID
    ).Distinct().Count() == DefIDs.Count();

This gets the number of unique DefIDs that are in your valid list and also have at least one "value == true" row. It then makes sure that the number of these valid DefIDs is equal to the expected value that you define above.

Answer 3

Here is an example using extension methods:

int[] ids = new int[] { 1, 2, 3 };

bool allFound = Settings.Where( s => s.Value && ids.Contains( s.DefID ) )
                        .Select( s => s.DefID )
                        .Distinct()
                        .Count() == ids.Length;

Answer 4

If you are ok with having a ValidSettings or SettingsMaster table, you could:

bool allFound = myContext.SettingsMaster.All(m=> m.Settings.Any(s=>s.Value));

For the current tables version, I would

int[] allIds = new int[] {1, 2, 3};
var validIds = (
    from s in myContext.Settings 
    where s.Value 
    select s.DefId
    )
    .Distinct().ToList();
bool allFound = allIds.All(id => validIds.Contains(id));