linq join incorrect syntax error

Question

I am using LINQ query to get the rows with certain conditions. Here is the query.

var query = from v in dt1.AsEnumerable()
      join c in dt2.AsEnumerable() on v.Field<int>("ID") equals c.Field<int>("ID")
      where v.Field<string>("col1").Equals("abcd") 
      && (c.Field<string>("col1").Equals("8776") || c.Field<string>("col1").Equals("8775"))
      select new 
          {
             ok = (from a in v where v.Field<string>("stah").Equals("1") select a).count(),
             ok1 = (from a in v where v.Field<string>("stah").Equals("2") select a).count(),
             ok2 = (from a in v where v.Field<string>("stah").Equals("3") select a).count()

          };

The error is present in

ok = (from a in v where v.Field<string>("stah").Equals("1") select a).count()

The error is

could not find an implementation of the query pattern for source type 'system.data.DataRow'. 'Where' not found

Sample Input : dt1

iD      col1      stah
1       4567        1
2       8748        2
3       3487        3
4       8776        1

dt2

iD      col1
1       4754
2       4576

Output

Get count of all rows where stah=1 && dt2.col1='4754'

But I cannot get it working. What is the correct syntax for this ?

Answer 1

@HarshitShrivastava mentioned that my previous attempt at the query didn't take into account all the where conditions.

How about this version using a mix of Linq query and Linq Lambda:

var query = from dataRows1 in dt1.AsEnumerable().Where(r => r.Field<string>("col1").Equals("abcd"))
               join dataRows2 in dt2.AsEnumerable().Where(r => r.Field<string>("col1").Equals("8776") || r.Field<string>("col1").Equals("8775"))
                  on dataRows1.Field<int>("ID") equals dataRows2.Field<int>("ID") into b
    select new
    {
        id = dataRows1.Field<int>("ID"),
        ok = (from a in b where a.Field<string>("stah").Equals("1") select a).Count(),
        ok1 = (from a in b where a.Field<string>("stah").Equals("2") select a).Count(),
        ok2 = (from a in b where a.Field<string>("stah").Equals("3") select a).Count()
    };

Note: I included the ID field in the projected output just for verifying the results. Remove as needed.

Answer 2

If I have understood you correctly, then this is what you need:-

var query = dt1.AsEnumerable()
               .Where(x => x.Field<int>("stah") == 1 
                       && dt2.AsEnumerable()
                             .Any(z => z.Field<int>("Id") == x.Field<int>("Id") 
                                      && z.Field<string>("Col1") == "4754")
                     ).Count();