0

How to convert sql query to linq using datatable.

select DISTINCT targeturl from internal where TargetURL NOT in (select DISTINCT url from Internal)

using this query, but still not getting the correct result.

  var List1 = dt.AsEnumerable().Select(c => c.Field<string>("URL")).Distinct().ToList();
  var List2 = dt.AsEnumerable().Select(c => c.Field<string>("TargetURL")).Distinct().ToList();
  var Targetname = List2.Except(List1.ToList()).ToList();
Musakkhir Sayyed
  • 7,012
  • 13
  • 42
  • 65
  • Have you seen http://stackoverflow.com/q/857973/50447 - it should just be a case of adjusting the collection of items, and negating the where clause. – Rowland Shaw Feb 11 '15 at 09:03

3 Answers3

0

I prefer to separate first 

dim query = (from u in Internal select u.url).distinct

Second

dim tmp = (from t in Interal where not query.contains(TargetURL) select TargetURL ).ToList

It's in VB.net but You can translate easily And you can too distinct with group by request.

YannickIngenierie
  • 602
  • 1
  • 13
  • 37
0

To write SELECT DISTINCT targeturl from internal where TargetURL NOT in (select DISTINCT url from Internal) in a linq lambda expression, you could use the following:

var result = Internal .Select(a => a.TargetURL) .Where(a => !Internal.Select(i => i.url).Distinct().Contains(a.TargetURL));

Or break it out a little as: var distinctUrls = Internal.Select(i => i.url).Distinct(); var result = Internal.Select(a => a.TargetUrl) .Where(a => !disctinctUrls.Contains(a.TargetUrl));

The above creates an IEnumerable of urls, which we use as an exclusion in the where predicate.

chrisc
  • 434
  • 3
  • 9
  • How to use it with datatable? – Musakkhir Sayyed Feb 11 '15 at 10:00
  • I am using this way but still not getting the correct result. var List1 = dt.AsEnumerable().Select(c => c.Field("URL")).Distinct().ToList(); var List2 = dt.AsEnumerable().Select(c => c.Field("TargetURL")).Distinct().ToList(); var Targetname = List2.Except(List1.ToList()).ToList(); – Musakkhir Sayyed Feb 11 '15 at 10:02
0

You can also try populating an object then using the object to populate the datatable.

var data = Select(c => c.Field<string>("TargetURL")).Distinct().ToList();
Datatable dtMyTable = new Datatable();
dtMytable.Columns.Add("col1",typeof(string));
dtMytable.Columns.Add("col2",typeof(string));
dtMytable.Columns.Add("col3",typeof(string));

then populate the table

foreach (var item in data)
{
      dtMytable.Rows.Add(data.col1,data.col2,data.col3);
}
Thabane
  • 48
  • 7