Fetching value from a datatable into datatable with where clause
var res = db.dtTable.Where(x=> x.Value > 0).ToList();
OP: No its not Database Table its DataTable.
DataTable filteredTable = dtTable.AsEnumerable().Where(row => row.Field<int>("Value") > 0).CopyToDataTable();
OP: sorry the datatype is int64, its giving the following error 'Cannot cast DBNull.Value to type 'System.Int64'. Please use a nullable type.'
DataTable filteredTable = dtTable.AsEnumerable().Where(row => row.Field<Int64?>("Value") > 0).CopyToDataTable();
Get specific value from datatable with where-clause
query
will be an IQueryable<DataRow>
, so st
will be a DataRow
. Try this:
foreach (var st in query)
{
MessageBox.Show(st.Field<int>("id").ToString());
}
Or if you know there will only item with that item_name
, here's an alternative version which does essentially the same thing, but is probably a bit easier to understand:
var st = producten.Rows.Cast<DataRow>().FirstOrDefault(x => x.Field<string>("item_name") == test);
if(item != null)
{
MessageBox.Show(st.Field<int>("id").ToString());
}
Select from datatable with where clause
You could use,
dt.where(e => {check something}).Select({select code here})
Do this on both the places. Hope this helps.
Get a cell value from datatable with two condition in where clause with LINQ
You are querying the wrong column. The code should be:
var query = from r in dt.AsEnumerable()
where r.Field<string>("Key") == "Trainee"
where r.Field<string>("Sub_Key_Name") == "BM"
select r;
If you only need the Sub_Key_Value
you should try:
var query = from r in dt.AsEnumerable()
where r.Field<string>("Key") == "Trainee"
where r.Field<string>("Sub_Key_Name") == "BM"
select r.Field<string>("Sub_Key_Value");
Select statement on DataTable without where clause using Linq
fullTable
.AsEnumerable()
.Select(x => new
{
PartnerID = x.Field<int>("PartnerID"),
PartnerName = x.Field<string>("Partner Name")
})
.Distinct();
This will create an anonymous type with the two properties you want. You than apply a Distinct
to remove the duplicates. Anonymous types handles GetHashCode
and Equals
for you which Distinct
uses to identify duplicates.
Filtering a datatable row using a where clause
The Select method of a DataTable returns an array of DataRow even if your query selects only one row
DataRow[] dr = dtTable.Select("VendorID = " + Session["VendorId"].ToString());
Then, if you really expects just one row, you could easily grab the expected row checking for the length of the array. In this case it is my opinion that no fancy Enumerable extension methods are really needed
if(dr.Length > 0)
{
string avalue = dr[0]["AColumnName"].ToString();
...
}
union three select queries with where clause into one datatable
i believe i found a solution not sure if its the easiest to implement in vb.net code, ill have to call the temp table and usually has a long winded name auto generated. #TEMP_____________231651651 for example is one i got back for another application. but anywho this is what we came up with
Go
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP Table #Data
SELECT A.ItemId ,A.RefId ,B.EquipmentName ,C.PointId ,C.PointDescription,
D.InspectionTimeStamp ,D.AlarmCode,D.AlarmDescription ,A.RouteId ,A.Type ,A.SequenceNo
,A.Skip ,A.Item INTO #Data FROM [Trident].[Maintenance].
[BasicCareEquipmentPointDetails] A INNER JOIN [Trident].[Maintenance].[Equipment] B ON
A.ItemId = B.EquipmentId INNER JOIN [Trident].[Maintenance].[BasicCarePointDetails] C
ON A.RouteId = C.RouteId LEFT OUTER JOIN [Trident].[Maintenance].
[BasicCareInspectionHistory] D ON C.PointId = D.PointId AND D.InspectionTimeStamp
BETWEEN '03/06/2020' AND DATEADD(DAY, 1, '03/06/2020') WHERE A.RouteId In
('RG00000792', 'RG00000800', 'RG00000801') AND A.Skip = 0 AND A.ItemId =
C.TemplateId ORDER BY A.RefId, A.SequenceNo Desc
SELECT *
FROM (SELECT ItemId
,RefId
,EquipmentName
,PointId
,PointDescription
,InspectionTimeStamp
,AlarmCode
,AlarmDescription
,RouteId
,type
,SequenceNo
,Skip
,Item FROM #Data) AS s
PIVOT (
MAX(AlarmCode)
FOR RouteId IN ([RG00000792], [RG00000800], [RG00000801])
) p
ORDER BY RefId, SequenceNo DESC
Using the pivot feature. not sure if aggregating the AlarmCode column is the cleanest though, even though its not doing a max on a string value
Related Topics
How to Delete a Read-Only File
Getting All Attributes from an Iwebelement With Selenium Webdriver
The Incoming Request Has Too Many Parameters. the Server Supports a Maximum of 2100 Parameters
Can Newtonsoft Json.Net Skip Serializing Empty Lists
How to Acces an Instance of a Class from Another Class
Check for Internet Connection Constantly
Change Datagrid Cell Colour Based on Values
C# - How to Save Byte Values to File With Smallest Size Possible
How to Connect to a Database in ASP.NET Core Without Entity Framework
Compare Two Objects' Properties to Find Differences
Client Certificate Not Getting Added to the Request (Certificate Verify)
How to Get the Specific Column in Excel Worksheet Using Documentformat.Openxml C#
How to Autosize the Height and the Width of C# Windows Form
C# Lamba With Datetime and Between
What Regular Expression Would I Use to Remove Everything After the Second Backslash