I would like to get the distinct value form DataTabel using LINQ.
For example my sample table,
[code]....
1. How to get the distinct Country from Country you like column and the return records as Array or DataTable? (I want only India,USA,UK,Germany )
Is there any way to filter the record values using LINQ?
I have a datatable which has been dynamically generated from FoxPro tables using a UNION Select statement. e.g.
SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM Z ORDER By v_alue1
This produces a datatable with about 100 rows, each containing many fields, one of which is c_olor. From this datatable, I would like to select the distinct colors and then output in a dropdown.
I have a public class Color which just has one property which I can then use as the DataTextField and DataValueField for the dropdownlist
[code]...
However this never results in the distinct colors.
I have searched and searched for what I am looking for, and this seems to be one of the methods to produce a distinct set of results, but this and the others do not work.
My reasoning behind getting the colors this way, is that I need to get various other distinct values from the same UNION SELECT datasource, so would just do one DB call, cache the results, and then just used this cached datasource to retrieve all my distinct values.
I am using C# + VSTS 2008 + .Net + ASP.Net + IIS 7.0 + ADO.Net + SQL Server 2008. I have a ADO.Net datatable object, and I want to filter out duplicate/similar records (in my specific rule to judge whether records are duplicate/similar -- if record/row has the same value for a string column, I will treat them as duplicate/similar records), and only keep one of such duplicate/similar records.
The output needs to be a datatable, may output the same datatable object if filter operation could be operated on the same datatable object.
What is the most efficient solution?
how can i check distinct val in col_1 of datatable and assign seq no to each distinct val of col_1 to col_2?
View 6 RepliesI have a datatable dt with 4 columns. I want to retrieve only distinct values from column1 of datatable.
How can I achieve this using c# code?
I have an already created DataTable object which i am using for my girdview (asp.net) i also need to bind a column of that object to a DropDownList. The datatable has the correct details in the column but the column contains more that 1 of the same name in the column - hence I would love to just do some kind of SELECT DISTINCT on the datatable and copy it to a new datatable for use with binding the dropdown.
This would allow me to save resources by making another trip to the database.
Here is an example, the current datatable has a column called items and in this column has the following entries
1
1
1
1
5
5
6
And of course i need only unique items for binding to my dropdown, hence i need the following data
1
5
6
Of course i don't want to change the original datatable object but rather make a copy of it will the new details.
I'm trying the pull out a result that shows me how many visitors I have had each day.
My database looks like this.
[code]....
I have a database that is filled with documents. In the database there is the documents id, name and version. Two documents can have the same name and id but are seperated by different versions.For instance there can be two entries, id: A44, name: Dilbert was here. But they are version A and B.When retrieving these to a listview I wish to limit the documents shown to only one version. Doing so means that I am only to show one document with the same id. I only get the id and name to show in the listview so I thought that distinct might do the trick. But since the name can change throughout different versions this was no good.Basically I wish to retrieve all documents but with distinct id. How do I achieve this?
View 10 RepliesI have this group-join syntax but I couldn't get the distinct value from the relational tables below.
Table_1
key_1 t1_value
1 Food
2 Sports
3 Leisure
4 Trip
5 Zoo
Table_2
key_2 key_1 t2_boolean
15 1 True
16 1 True
17 1 True
18 2 True
19 2 True
20 2 False
desire result:
[code]...
I have the following table structure:
[code]....
The following query returns the default recordset:
[code]....
I would like to select a distinct query based on three fields and display the rest of the fields. For example,
I have firstname, lastname, address, city, state and zip, but I only want to use the distinct on these fields first and last name. However when i use the distinct function I get a distinct on all the selected rows. I just want to distinct firstname and lastname and display the other fields. for example,
Mary Smith New York
Mary Smithy New York
Mary Smith Maine
I would like the result to be:
Mary Smith New York
Mary Smithy New York
I don't care about Mary Smith Maine, because i am only using the distinct for "Mary Smith" firstname and last name and showing the city.
I'm trying to build a linq2Entity/gridview function for a forum, that draws data from multible tables.The result should be: Get the latest 10 threads that user(xx) has replyed in, that is not disabled or has a subject that is not allowed.Include information on the last post, thread owner user, and last post user, in each thread.
ere is my code... is returns the correct threads and some that should not be there.
[Code]....
I am struggling to create a webservice method using LINQ technology and to populate a dropdown in a form. Here is the Webservice code.
[WebMethod]
public
string[] GetAllCountries()
{
LinqClassesDataContext db =
new
LinqClassesDataContext();
var q =
from c
in db.ListContinents()select
c;
return q.ToList() ;
}
And here is the form code behind
protectedvoid
Page_Load(object sender,
EventArgs e)
{
WSClass WS =
new
WSClass();//bool
ds = WS.GetAllCountries();
var v = WS.GetAllCountries();
ddlContinent.DataSource = v;
ddlContinent.DataBind();
}
Many times I need to manipulate with DataTable after I get data from database using adapter.Fill(table).
Usually I calculate data in DataTable by using loops. But I know that there is simpler way using LINQ. I know some basics about that just for strongly typed objects.I can't find how to do this with DataTable that is not strongly typed. How could I get all DataRow or Filtered DataTable (not just value for the field) from table for that conditions:
- where the specified field of the table contans MAX/MIN/AVG value,
- where the specified field of the table equals to some value.
I got the following query to group the table. I am wondering how to add a subtotal record with anonymous type.
string prevOriginator = string.Empty;
var oOriginator = (from I in dtIssue.AsEnumerable()
group I by new {RaisedBy = I.Field<string>("RaisedBy"), ItemType = I.Field<string>("ItemType")} into grp
[code]...
Is there option to convert from Linq.IQueryable to DataTable with some sort of CopyToDataTable() function. I am trying this.
View 2 RepliesI have a data table I need to convert to XML. I know I can use datable.WriteXML but I need to rename the elements as the column names coming from the Oracle db are ugly. I found this snippet somewhere:
[code]....
It looks like what I want but when I paste it in my code it shows an error on select Cannont convert lambda expression to type 'string' because it is not a delegate type.
I am new to linq and for that matter to C# (done a lot more vb). I am going to read the books but I need to get this done pretty quick for a project.
I wish to retrieve data from a DataTable using Linq, and I do this by first creating a DataTable with the data and then using Linq to query:
[Code]....
My problem is, that the column "enddatee" has 'holes', meaning that some rows does not have a value. This means that the convertion to DateTime will fail for some rows. How can I get around that?
how to convert a linq query to a datatable
this is my query
[Code]....
how can i delete a row in datatable using linq to dataset?
I have a dataset in my project, need to delete some rows filtered by ID
how can i do?
using linqs how to fill datatable using linq to sql in asp.net
i have one storedprocedure to select data from table
and one s.p for insert the data and one for update the data
how i use these s.p to insert and update the data using linq. to sql
I am gettin error in this code
[Code]....
I'm having a spot of trouble figure out how to write my linq query so that is allows a decimal field in my datatable to be null
Right now I have this:
[Code]....
But it's not working. The error I am getting is: Cannot cast DBNull.Value to type 'System.Decimal'. Please use a nullable type.so how do I get it to allow program_ar_id to be null?
I call a stored procedure using linq. I want to convert the ISingleResult output to datatable. what can I do?
DataClassesDataContext Dac = new DataClassesDataContext();
var ExitResult = Dac.Retrieverelations(WorkshopCode);
now I'm using below code for converting but I need a better solution.
D
dataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(Int64));[code]....
how to extract DataTable or DataSet from Linq Query or List. e.g I have linq query like this
MyDBpDataContext dcSp = new MyDBpDataContext();
dcSp.myProgrammSP().ToList();
I wrote an Sp which sends a Table as result and I have code which is already using DataTable So I want to convert this result in DataTable.
I am converting my datatable to LISt using LINQ how do I handle nulls coming from database
List<Port> portDetails = new List<Port>();
DataTable dt = ds.Tables[0];
portDetails = (from q in dt.AsEnumerable()
select new Port
{
PortCode = q.Field<string>("Code"),
ExtCode = q.Field<string>("Nb"),
Name = q.Field<string>("Name"))
}).ToList();
In the above query if Code is null I do not want property portcode to be set to the value it should only set if it is not null or not blank
PortCode = q.Field<string>("Code"),
What should be syntax
I was trying somethign like this which doesnt work
Portcode = q.Field<bool>("Code") == null ? null : q.Field<bool>("Code")