Johny Bravo Johny Bravo - 1 month ago 15
C# Question

Merge two datatables with check c#

I am having two datatables as below,

dtOrigin
RowId Stk ProdName
2 245 ABC
4 144 XYZ
5 122 ADE


dt1
RowId Stk
2 2
4 7


I need to merge this two datatables to produce below result, basically if rowid exist in dt1, need to minus its stock qty from dtOrigin
dtNew

RowId Stk ProdName
2 243(245-2) ABC
4 137(144-7) XYZ
5 122 ADE


I am able to do this with loop, but is there anyway to do this without loop
Thanks

var JoinResult = (from p in dt1.AsEnumerable()
join t in dt2.AsEnumerable()
on p.Field<string>("RowID") equals t.Field<string>("RowID")
into joinedtables from stuff in joinedtables.DefaultIfEmpty()
select new
{
----------------,
----------------,
Stock = p.Field<Int32>("Stk") - stuff.Field<Int32>("Stk")
}


Throwing exception. Can you please correct?




Below is the code I a using

var JoinResult = (from p in dt.AsEnumerable()
join t in dt2.AsEnumerable()
on p.Field<string>("RowID") equals t.Field<string>("RowID")
into joinedtables from stuff in joinedtables.DefaultIfEmpty()
select new
{
RowID = p.Field<string>("RowID"),
ProdName = p.Field<string>("ProdName"),
STK = p.Field<Int32>("STK") - stuff?.Field<Int32>("STK") ?? 0
}

dtable = LINQResultToDataTable(JoinResult);




public static DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)
{
DataTable dt = new DataTable();


PropertyInfo[] columns = null;

if (Linqlist == null) return dt;

foreach (T Record in Linqlist)
{

if (columns == null)
{
columns = ((Type)Record.GetType()).GetProperties();
foreach (PropertyInfo GetProperty in columns)
{
Type IcolType = GetProperty.PropertyType;

if ((IcolType.IsGenericType) && (IcolType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
IcolType = IcolType.GetGenericArguments()[0];
}

dt.Columns.Add(new DataColumn(GetProperty.Name, IcolType));
}
}

DataRow dr = dt.NewRow();

foreach (PropertyInfo p in columns)
{
dr[p.Name] = p.GetValue(Record, null) == null ? DBNull.Value : p.GetValue
(Record, null);
}

dt.Rows.Add(dr);
}
return dt;
}

Answer

Try this:

var JoinResult = 
                 ...
                 select new {
                     ...
                     Stock = p.Field<Int32>("Stk") - (stuff?.Field<Int32>("Stk") ?? 0)
                 };

I'm guessing that when there is no matching record in the second datatable, stuff will be null, causing a NullReferenceException when trying to read the value for that row. This expression:

stuff?.Field<Int32>("Stk")

means "if stuff is null, then the whole expression should evaluate to null, otherwise it should return the value from the field."

That is still not enoguh; because you can't subtract null from something else. It needs to be passed to the ?? operator:

stuff?.Field<Int32>("Stk") ?? 0

which means that if the left side is not null then use that value, otherwise use 0.