Help Convert sql query into linq

Help Studio
, ,
1

HI ,

i want to convert below sql query into linq.

Select DISTINCT internal.[Name],Quaility.[Trade]
from internal Inner Join Quality on (internal.[name]=Quality.[Name]) and (format(internal.[trade date],“mm/dd/yyyy”)=Quality.[trade date])
where (((Internal.Reason)=‘Fee needs’) and ((quality.[qty])*(Quality.[price])<>0))
group by Quality.[Name]

can you please help here to decode

2

Hi @TUSHAR_DIWASE

Try this LINQ:

result = (From internalRow In internalTable.AsEnumerable()
              Join qualityRow In qualityTable.AsEnumerable()
              On internalRow.Field(Of String)("Name") Equals qualityRow.Field(Of String)("Name") And
                 internalRow.Field(Of DateTime)("trade date").ToString("MM/dd/yyyy") Equals qualityRow.Field(Of DateTime)("trade date").ToString("MM/dd/yyyy")
              Where internalRow.Field(Of String)("Reason") = "Fee needs" And
                    (qualityRow.Field(Of Decimal)("qty") * qualityRow.Field(Of Decimal)("price")) <> 0
              Select Name = internalRow.Field(Of String)("Name"),
                     Trade = qualityRow.Field(Of String)("Trade")
              ).Distinct().GroupBy(Function(x) x.Name).Select(Function(g) g.First())

Hope it helps!!

3

@TUSHAR_DIWASE
var result = (from internal in internalCollection
join quality in qualityCollection
on new { internal.Name, TradeDate = internal.TradeDate.ToString(“MM/dd/yyyy”) }
equals new { quality.Name, quality.TradeDate }
where internal.Reason == “Fee needs” && (quality.Qty * quality.Price) != 0
group quality by quality.Name into g
select new
{
Name = g.Key,
Trade = g.Select(q => q.Trade).Distinct()
}).Distinct();

4

thanks @Parvathy
i was trying to get result as datatable so i add .copytodatatable in your code but it doesnt work
dt result = ((From internalRow In internalTable.AsEnumerable()
Join qualityRow In qualityTable.AsEnumerable()
On internalRow.Field(Of String)(“Name”) Equals qualityRow.Field(Of String)(“Name”) And
internalRow.Field(Of DateTime)(“trade date”).ToString(“MM/dd/yyyy”) Equals qualityRow.Field(Of DateTime)(“trade date”).ToString(“MM/dd/yyyy”)
Where internalRow.Field(Of String)(“Reason”) = “Fee needs” And
(qualityRow.Field(Of Decimal)(“qty”) * qualityRow.Field(Of Decimal)(“price”)) <> 0
Select Name = internalRow.Field(Of String)(“Name”),
Trade = qualityRow.Field(Of String)(“Trade”)
).Distinct().GroupBy(Function(x) x.Name).Select(Function(g) g.First())).copytodatatable

getting error copytodatatable is not a member of system.collection.generic.ienumerable(of anonymous type)

5

Hi @TUSHAR_DIWASE

Try this:

dtResult = (From internalRow In internalTable.AsEnumerable()
                             Join qualityRow In qualityTable.AsEnumerable()
                             On internalRow.Field(Of String)("Name") Equals qualityRow.Field(Of String)("Name") And
                                internalRow.Field(Of DateTime)("trade date").ToString("MM/dd/yyyy") Equals qualityRow.Field(Of DateTime)("trade date").ToString("MM/dd/yyyy")
                             Where internalRow.Field(Of String)("Reason") = "Fee needs" And
                                   (qualityRow.Field(Of Decimal)("qty") * qualityRow.Field(Of Decimal)("price")) <> 0
                             Select internalTable.Clone().LoadDataRow(New Object() {
                                 internalRow.Field(Of String)("Name"),
                                 qualityRow.Field(Of String)("Trade")
                             }, False)).CopyToDataTable()

Hope it helps!!

1 Like
6

Try this: result = (
from internal in internalList
join quality in qualityList
on new { internal.Name, TradeDate = internal.TradeDate.ToString(“MM/dd/yyyy”) }
equals new { quality.Name, TradeDate = quality.TradeDate }
where internal.Reason == “Fee needs” && (quality.Qty * quality.Price != 0)
group new { internal.Name, quality.Trade } by new { quality.Name } into g
select new
{
Name = g.Key.Name,
Trade = g.Select(x => x.Trade).Distinct().FirstOrDefault()
}
).Distinct()

1 Like
7

@Parvathy getting error - specified cast is not valid.
i guess it is because of datetime
what things i need to check.

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.