Linq To Datarow, Select Multiple Columns As Distinct?
Apr 16, 2010
basically i'm trying to reproduce the following mssql query as LINQ
SELECT DISTINCT [TABLENAME], [COLUMNNAME] FROM [DATATABLE]
the closest i've got is
Dim query = (From row As DataRow In ds.Tables("DATATABLE").Rows _
Select row("COLUMNNAME") ,row("TABLENAME").Distinct
when i do the above i get the error
Range variable name can be inferred only from a simple or qualified name with no arguments.
i was sort of expecting it to return a collection that i could then iterate through and perform actions for each entry. maybe a datarow collection?
As a complete LINQ newb, i'm not sure what i'm missing.
i've tried variations on
Select new with { row("COLUMNNAME") ,row("TABLENAME")}
and get:
Anonymous type member name can be inferred only from a simple or qualified name with no arguments.
to get around this i've tried
Dim query = From r In ds.Tables("DATATABLE").AsEnumerable _
Select New String(1) {r("TABLENAME"), r("COLUMNNAME")} Distinct
however it doesn't seem to be doing the distinct thing properly.
View 3 Replies
ADVERTISEMENT
Mar 9, 2012
I have a datatable as shown in the figure. Let me explain my required based on this image. I have 7 rows of data. The rows 1 and 2 contains columns till UnitSqcNo same. I want only row 3 among the two. In general I want select all the rows with distinct model, unittype, unit and rest with greater CompId. ie the table should look like
View 1 Replies
Aug 9, 2010
I have the following table structure. I want to select distinct CustomerId and CustomerName, TotalCost.[code]
View 2 Replies
Apr 2, 2010
I'm using LINQ to SQL to select some columns from one table. I want to get rid of the duplicate result also.
Dim customer = (From cus In db.Customers Select cus.CustomerId, cus.CustomerName).Distinct
Result:
1 David
2 James
1 David
3 Smith
2 James
5 Joe
Wanted result:
1 David
2 James
3 Smith
5 Joe
Can anyone show me how to get the wanted result?
View 3 Replies
Jul 11, 2011
I have an EnumerableRowCollection that looks like the following:
VendorCode | GroupType | Variance
01165 G .16
01165 G .16
01165 CH .16
01165 CH .18
07754 G .25
07754 G .25
07754 G .39
Essentially, this is a massive list of vendor codes, their groups, and price variances. I need to compose a query that will create a distinct list vendor codes and group types. The catch, however, is that I need to evaluate all of the variances associated with that particular VendorCode/GroupType to see if they are all the same - it they are not, I need to return some way of signifying that the group has a "custom" variance, otherwise it needs to return the value (ie: if they are all .16, then return .16, if there are multiple values, return "custom")
The result would look like this, based off of the list I showed above.
VendorCode | GroupType | Variance
01165 G .16
01165 CH custom
07754 G custom
I have no trouble getting a distinct list of VendorCode/GroupType - this is what I have so far:
Dim distinctList = From q In query Select q.VendorCode, q.GroupType, (evaluated q.Variance here?) Distinct
(where "query" is an EnumerableRowCollection(Of (anonymous type)))
I'm at a loss, though, on how to evaluate the variance property to get the result that I need?
View 1 Replies
Jul 13, 2010
I am trying to distinct on multiple columns and get datarows from datatable. but getting error.
Dim query As IEnumerable(Of DataRow) =
(From row As DataRow In SourceTable.AsEnumerable() _
Select row.Field(Of String)("ColumnName1"),
[Code]....
I want another datatable with distinct row based on given columns from SourceTable.
View 3 Replies
Aug 19, 2010
I have three tables Student, TimeSheet and TimeRecord.
Talbe columns:
Student : StudentId, FirstName,
LastName
TimeSheet: TimeSheetId,StudentId, IsActive
[Code].....
View 4 Replies
Nov 4, 2010
The following data is created by joining two sql tables together:I would like to group together distinct rows of DateStamp/UserName/ StudentName/ InstructorName/TableName/PrimaryKey (I'll call this 'group records') and then group under these ColumnName/PreviousValue/NewValue (I'll call this 'subgroup records')The end result would be that I could iterate through the 'group records' - there would be 5. In each 'group record', I could then iterate through the 'subgroup records'. The 5 groups would contain 3, 2, 5, 2 and 1 subgroup records respectively.What would be the syntax to create a query to do this? Ideally this would be in a vb.net linq syntax.
View 1 Replies
Mar 18, 2010
I can't quite figure out why this Linq Statement isn't working as i would expect:
[Code]....
I would assume that this would create a new collection of anonymous types, that would be distinct. Instead it creates a collection the size of the "ThisParentCollection" with duplicate "MyAnonymousType" in it (duplicate id's).
View 1 Replies
Aug 20, 2010
I have a single columned datatable inside a single tabled dataset.I just want to convert this dataset to distinct rows. Here is my code, it gives compile error '.' expected.
Dim query = _
From email In ds.Tables(0) _
Select email.Field<string>("Email").Distinct()
EDIT: I changed to (Of String) and it works... BUT NOW 'query' is an ienumerable collection of characters... not a datatable... so how do I convert back easily without manually doing a loop?
View 1 Replies
Jan 4, 2012
I've a collection with the data like this.[code]...
how can select the distinct data using linq?
View 2 Replies
Mar 28, 2012
I'm pretty new in Linq and the first problem I've is to select the Distinct values from a ObservableCollection( Of T)
I've tried with[code]...
View 2 Replies
Jul 14, 2010
I am trying to get distinct rows based on multiple columns (attribute1_name, attribute2_name) and get datarows from datatable using Linq-to-Dataset.[code]How to do thin Linq-to-dataset?
View 3 Replies
Oct 19, 2010
My particular example is fairly complex but I think the concept would apply equally to something like a logging system so I'll use that instead for ease of explanation. It's a ficticious example, please don't harp on or agonise over what is achitectually, programatically or morally wrong with the example itself
[Code]...
I don't want to 'cheat' and resort to a long-winded way of doing it when performance isn't critical here and I'm moderately confident it can be done in a single LINQ statement.
View 1 Replies
Aug 23, 2010
Dim orders = From tt In testTable _
Order By tt.Item("OrderNumber") _
Select tt.Item("OrderNumber"), tt.Item("OrderId")
This is breaking. Is there a way to do this? I would have thought it was easy enough. Obviously, I thought wrong....
View 1 Replies
Jul 23, 2012
I have a datatable where there are 3 columns
ID | Name | Date
and i have 4 columns
1 | ABC | 20/07/2012
[code].....
View 2 Replies
Mar 24, 2012
I have a Table:
[Code]...
I'm trying by LINQ-query (VB.NET) to return a count for distinct value of Objects, Stuffs and Colours for every Room:
[Code]...
View 1 Replies
Dec 6, 2011
I have a list of attachments that I need to group by clientCLID and EmailAddress. From this grouped list I only need a list of clientCLIDs. After fiddling for a while I've managed to get it to work as follows:
[Code]...
View 1 Replies
Feb 2, 2011
I'd like to take all suggestion either in C# or VB.NET.I have a DB diagram like the image below. I also include the database script here [URL]In the Students table, CountryId and RoomId column are allowed null. Because some records do not have info about room and country yet.Also, some students do not have essays.I'm doing a joint query with all tables. I want to select all students to project the result like this:Wanted query result.
Here's my current query that gives the result like the image below:
Dim db As New DBDataContext
Dim query = From st In db.Students _
Join c In db.Countries On c.Id Equals st.Id _
Join r In db.Rooms On r.Id Equals st.RoomId _
[code].....
current query result I got only one result back because I have (2) William NoMan record in every table. But I don't get anything about others, like (3) Sync Master who has everything but RoomId.What do I need to modify the query above so it will give me all students like in the wanted query image above?
View 2 Replies
Nov 16, 2009
I'm still trying to get my head around the whole "Lambda Expressions" thing.
Can anyone here give me an example ordering by multiple columns using VB.Net and Linq-to-SQL using a lambda expression?
Here is my existing code, which returns an ordered list using a single-column to order the results:
Return _dbContext.WebCategories.OrderBy(Function(c As WebCategory) c.DisplayOrder).ToList
Note: The WebCategory object has a child WebPage object (based on a foreign key). I'd like to order by WebPage.DisplayOrder first, then by WebCategory.DisplayOrder.
I tried chaining the order bys, like below, and though it compiled and ran, it didn't seem to return the data in the order I wanted.
Return _dbContext.WebCategories.OrderBy(Function(c As WebCategory) c.DisplayOrder).OrderBy(Function(c As WebCategory) c.WebPage.DisplayOrder).ToList
View 2 Replies
Nov 10, 2010
I am trying to write a module that will select cells from multiple, non-contiguous columns within a row that is defined by a variable. The column numbers are known and are unchanging. For example, my worksheet might look like this:
A B C D
Row 1 X X X X
I want to select cells A1, B1, and D1; however, I don't want to use the row number (1 in this case) in my code. Rather, I want the row number to be specified as a variable. The code I am writing is inside a For/Next loop, so I would like to use the iterative variable (i in this case) to define the row number. That way, every iteration of the For loop will select cells from a different row.
View 1 Replies
Aug 23, 2010
I have two tables Employees and CafeLogs. Some employees can be cashiers and also customers at the Cafe shop.
Table structures:
Employees: EmployeeId(PK) , FirstName, LastName
CafeLogs: LogId (PK), CashierId, EmployeeId, Value, => CashierId and EmployeeId are the data from column EmployeeId of Empoyee table
Table relationship:
Employees 1:N CafeLogs (CashierId (FK))
[code]....
Right now I know how to select only LogId, Employee's name, and , Value, not with Cashier name yet.
Dim query = From log In db.CafeLogs _
Join emp In db.Employees On emp.EmployeeId Equals log.EmployeeId _
Select log.LogId, emp.FirsName, emp.LastName, log.Value
View 1 Replies
Oct 25, 2011
I have a three column table which lists every person, every event they may have attended, and what percentage of the time they were there. I would really like to display this on one page with the person names on the side and the event names across the top.
Here' an example of what I have:
NAME EVENT %ATTENDANCE
Smith Rock Climbing 50
Allen Rock Climbing 78
[Code]....
View 2 Replies
Aug 4, 2010
I'm trying to fill a ComboBox from 3 columns in the same table. I need output DISTINCT and the drop down box would look better if it had no spaces between groups.
Using myCommand As New MySql.Data.MySqlClient.MySqlCommand("SELECT ten_id1, ten_id2, ten_id3 FROM tenants", myTenConnection)
Dim myReader As MySql.Data.MySqlClient.MySqlDataReader
[Code].....
View 2 Replies
Mar 24, 2009
i have to do the same work in vb as i have done in c#:
[URL]
View 1 Replies
Jul 7, 2009
How can i get the number of columns in A DataRow type?
View 9 Replies
Nov 18, 2010
When the user has typed in some information I would like to output possible matches in a descending order, so if someone types in a full first name and a full surname, it should be listed above a result where just the surname matches.I've done something similar in SQL before which worked perfectly, but this time I'd like to do it in LINQ.
Firstname, Surname, City, Country as string variables.
Dim DataEnum As IEnumerable(Of frmTelephone.clsPerson) = alPerson.OfType(Of frmTelephone.clsPerson)()
[code].....
View 4 Replies
Jan 21, 2011
I need to display a column called Full Name in the my datagridview, but the datasource (datatable) does not have a FullName column. It only has FirstName and LastName columns. I'm setting up my DataGridView like this:
[Code]...
View 1 Replies
Jan 20, 2011
im wondering which way may be better to find out the number of cols for a row:
1) row.Table.Columns then count
2) row.ItemArray then count
3) am i missing something, is there a much more "direct" way to do it?
View 2 Replies
Oct 14, 2009
I have a very interesting LINQ question. I have a document, that I am trying to filter results on, but to filter, I am matching on a REGEX result from one element of the XML. I have the following, working LINQ to XML to get the individual data that I'm looking for.
[Code]...
View 3 Replies