Member Avatar for lithium112
  var query = (from dt1 in dta.AsEnumerable()
               join dt2 in dtb.AsEnumerable()
               on dt1.Field<string>("column1") equals dt2.Field<string>("column1")
               into outer
               from Dt2 in outer.DefaultIfEmpty()
               group new { Dt2 } by new { SearchKey = dt1.Field<string>("SearchKey") } into g
               select new
               {
                   Status = g.Select(r => r.Dt2) == null ? string.Empty : g.Select(r => r.Dt2.Field<string>("Column2")).ToList()[0]
               });

               foreach (var row in query)
               {                   
                   dt.Rows.Add(row.Status.ToList()[0]);          
               }

So a rundown of the linq statement: I have 2 datatables which I am using a left join with. I am trying to query the second datatable with a group by. I check for null, and if it is null, I give it a string.Empty otherwise it should contain the value of the column. I then use a foreach statement to loop through the rows, but it will hit a null value and give me the error "Value cannot be null. Parameter name: row". It's like it doesn't even check for nulls. Does anyone happen to know how to fix this? I have this in a try catch to catch the error if anyone tries to reproduce it. Any help would be much appreciated. Thanks!

Since the values are coming from a database, I'm wondering if using DBNull would work instead of null

Member Avatar for lithium112

Actually the values aren't from a database. Sorry, should have mentioned that. They come from a cobol file and then I put it in a datatable. I did try DBNull just in case, but it gives an error unfortunately.

You might need:

foreach (var row in query.Where(item => item.Status != null))

Without code/data that can be tested/debugged it's hard to find another way.

Member Avatar for lithium112

I just tried what you suggested and it didn't work. I've got it checking for nulls in 3 places and it still won't check for the nulls for some reason. This is a wierd one. I may just have to loop through the datatable and remove any null values before I perform the Linq expression. I was hoping for a faster and more concise solution, but nothing seems to be working unfortunately.

In order to give you an example of the data, column 2 has some nulls in it. So for example I have a row such as Row1: 1 Hats Row2: 2 Shirts Row3: 3 Null and so on. Obviously it is different data, but that is essentially what it does not like in Row 3. It will take row 1 and 2, but it will not skip row 3 no matter what conditions I have. I can get it to work without the group by, but I need the group by. Thanks for your help and any further help anyone might have! I'll just loop through the datatable for now until I can find a better solution or someone knows of something I don't.

Member Avatar for lithium112

Ok, I may have been looking at this backwards. It is on column 1 in dt1 where it is null. So from what I have, I am trying to join the datatables where a couple of the columns in dt1 column1 has nulls. So it is on the linq expression where it throws the error.

Member Avatar for lithium112

I finally figured it out. In order to use Where with a join, it is a little bit different syntax.

var query = (from dt1 in dta.AsEnumerable()
               join dt2 in dtb.AsEnumerable()
               on dt1.Field<string>("column1") equals dt2.Field<string>("column1")
               where dt1.Field<string>("column1") != null
               //into outer
               //from Dt2 in outer.DefaultIfEmpty()
               group new { dt2 } by new { SearchKey = dt1.Field<string>("SearchKey") } into g
               select new
               {
                   Status = g.Select(r => r.dt2.Field<string>("Column2")).ToList()[0]
               });

I really do appreciate everyone's help on this. I definitely learned a couple things through this. Thanks guys!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.