Linq query to merge datatable in single row based on condition

[Akshay_Suryawanshi]

Hi Guys, I have a problem in which I need your help to create a linq query with three different conditions.

so I have this master file eg below.

So I have to exclude blank and soft book row from Appointment number column and take only unique appoint number example here I will get list of unique appointment number 1 and 2 …so I will do it with defaultview. so forget it and only think you need to know is I have master dt and a list of unique appointment number fetched from that master_dt. And I will be looping on these unique number list

So here are the condition on which I need Linq query on.

Condition 1:
Appointment number and Boat No will be same.
so when this condition meets its should merge all the rows.

example :

the output of this will be as given below

here column A values are merged into one, column B take value of highest weight row so here take value from 5th column since that has 543.6, column c all rows merged in one, column E merged in one, column F single value i.e. value of row highest weight and column G with merge of all rows…

here \ is the delimiter to merge rows and flag1 is default value new column is added.

Condition 2:
Appointment number is same and Boat No is different.
so when this condition meets its should get the value of A column with highest weight and first four digits of date column.

example :

the output of this will be as given below

so here you can see row n has greater weight 543.6 among other two 220.6 and 227.6… so I have to merge A column value i.e row n and First digit of date column that is 1117 and I will get the value like row n1117 and remaining all column merge logic is same.
here \ is the delimiter to merge rows and flag1 is default value new column is added.

Condition 3:
Appointment number is same and Boat No is blank.
so when this condition meets its should get the value of Po No. column with highest weight which is same for every condition.

example :

the output of this will be as given below

so here you can see row n has greater weight 543.6 among other two 220.6 and 227.6… so I have to PO number 13
here \ is the delimiter to merge rows and flag1 is default value new column is added.

Condition 2 and condition 3 are same. just that value of Boat No. changes every time.

please help @NIVED_NAMBIAR , @ppr you are the expert in linq please help

[Akshay_Suryawanshi]

@ppr please help!!!

[ppr (Peter)]

@Akshay_Suryawanshi

It is not recommended to to do all processing within a single LINQ. The Main Building blocks could look like this:

Grouping the data with a LINQ and processing the group and its members within a for each

Checking the types we can do:

the sample was done for the first group

for other group processing find some starter help here:

Intro_GroupBy1Col_grpProcessing.xaml (13.0 KB)

Also have a look here:

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

[Akshay_Suryawanshi]

Hi Peter,

Yes I want to create three different linq. Can you provide me linq for second or third condition. I will create it for other two.

[ppr (Peter)]

do a start with the given, all essentails were shared with you

[Akshay_Suryawanshi]

Hi @ppr,

I am almost done with two condition but I stuck with 2nd condition where appointment number is same but the boat no is different. Can you help me where I am going wrong.

(From d In dt_Master.AsEnumerable Where String.IsNullOrEmpty(d(“boat no”).ToString)
Group d By k= d(“Appointment number”).toString.Trim, k1 = d(“boat no”).distinct().count = 1 Into grp=Group