C# :: Aggregate Or Join Strings In Linq To Sql Query (SQL Server)?
Aug 6, 2010Given a table like
ID | Name | City
1 | X | Y
2 | Z | Y
[code].....
ADVERTISEMENT
Multi - Table Join Query In ASP.NET To SQL Server Via LINQ?
Jan 29, 2012I'm working on an ASP.NET 4.0 site, which I inherited ownership of. It has a number of existing LINQ datasources pointing to individual tables. For example, to the Patient Encounter Summary table in SQL Server. The problem is in displaying the data there. It's a normalized database, so that table contains (for example) the provider ID, rather than the provider name.
It's simple enough to join the Patient Counter Summary to the Providers table (in SQL)... but how does one do that in ASP.NET? I'm not sure of the correct nomenclature, but the 'mid layer' is VB.
[Code]...
Converting A Traditional SQL Aggregate Query Into A LINQ One - SUM Grouping ?
May 24, 2010I'm having trouble getting my head around converting a traditional SQL aggregate query into a LINQ one. The basic data dump works like so:
Dim result =
(From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
[CODE].....................
What I need to really be getting out is ih.Period (a value from 1 to 12) and a corresponding aggregate value for i.ExtendedValue. When I try to Group ih I get errors about i being out of scope/context.
Asp.net LINQ Join Query
Sep 28, 2011I have this query that I tried to join 2 tables together, one which holds the product name, and product numbers, and take a product number, and go to the other where to find a Art_no which is like the product number.[code]
View 2 RepliesJoin LINQ Query Result And Arraylist In VB
Dec 5, 2009I have a set off LINQ queries filtering original datatable (loaded from Excel file) according to user selections in Comboboxes. Then the result is joined with Arraylist to further filter the set. Arraylist is a simple string list from 1 to 5. [Code]
View 3 RepliesAsp.net - SQL Query Conversion To LINQ Left Outer Join?
Jul 4, 2009I've looked all around and spent way to long trying to convert this SQL statement into a Linq statement in VB. I'm sure it would be a good example for others out there - the statement is trying to pull products that have a many-to-many relationship with product categories, and the categories have a hierarchy of parents/children.
Here is the query I am trying to convert:
SELECT P.ProductID, P.ProductName, P.ProductSlug, P.PartNumber
FROM Products AS P
INNER JOIN Products_Categories AS PC ON PC.ProductID = P.ProductID
[code]....
I can get up to the point where I am trying to say "WHERE ... (P_Cats.Parent = 9)" but can't figure that part out.
LINQ Join Query On Datatable And Arraylist Error?
Dec 6, 2009I have a set of LINQ queries filtering original datatable (loaded from Excel file) based on user selections in Comboboxes. Then the result is joined with Arraylist to further filter the set. Arraylist is a simple list of strings.
Query:
If (Samples.Item(0) Is Nothing) = False And Samples.Item(0) <> "All samples" Then
queryResults = From records In queryResults Join samp In Samples _
On records("SAMPNUM") Equals samp _
[Code]....
LINQ Query - Join Tables On Nullable Columns
Apr 17, 2011How to JOIN tables on nullable columns? I have following LINQ-query, RMA.fiCharge can be NULL:
Dim query = From charge In Services.dsERP.ERP_Charge _
Join rma In Services.dsRMA.RMA _
On charge.idCharge Equals rma.fiCharge _
Where rma.IMEI = imei
Select charge.idCharge
I get a "Conversion from type 'DBNull' to type 'Integer' is not valid" in query.ToArray():
Dim filter = _
String.Format(Services.dsERP.ERP_Charge.idChargeColumn.ColumnName & " IN({0})", String.Join(",", query.ToArray))
So I could append a WHERE RMA.fiCharge IS NOT NULL in the query. But how to do that in LINQ or is there another option?
The problem was that the DataSet does not support Nullable-Types but generates an InvalidCastException if you query any NULL-Values on an integer-column. The modified LINQ-query from dahlbyk works with little modification. The DataSet generates a boolean-property for every column with AllowDbNull=True, in this case IsfiChargeNull.
Dim query = From charge In Services.dsERP.ERP_Charge _
Join rma In (From rma In Services.dsRMA.RMA _
Where Not rma.IsfiChargeNull
Select rma)
On charge.idCharge Equals rma.fiCharge _
Where rma.IMEI = imei
Select charge.idCharge
Sql Server :: LINQ To SQL Get Grouped MIN With JOIN?
Apr 19, 2010I'm having trouble with a LINQ to SQL query getting the min value using Visual Basic. Here's the SQL:
SELECT RC.AssetID, MIN(RC.RecCode) AS RecCode, JA.EngineerNote from JobAssetRecCode RC
JOIN JobAssets JA ON JA.AssetID = RC.AssetID AND JA.JobID = RC.JobID
WHERE RC.InspState = 2 AND RC.RecCode > 0
[code]....
.net - Write A Linq To Sql Query To Find Records Where Field Name Can Match One Of Dynamic Number Of Strings?
Mar 21, 2012I have users check off lab facilities in a UI. I want to use linq to fetch corresponding records for all of the labs that they have checked off. Basically,
Dim myRecs = (From l As EpiData In myDataContext.EPIDatas Where l.facility= _
one of the checked labs
So basically, I need to write a linq query where the "strings" to match are determined at runtime. Is there any way to do this easily? I know that there is a library out there called dynamic LINQ, but (1) it's in C# and I'm writing in VB (2) I'm really just looking for a single, simple solution for this single case.
How To Use Aggregate Function In LINQ
Nov 1, 2011I want to get the average of this query.
From d In (From c In location.Descendants("temperature")
Where c.Attribute("type").Value = "hourly" Select c).Descendants("value")
Take 3
Select d
I can see the aggregate keyword in the autocomplete dropdown, but how to apply it.
LINQ - Logical Difference Between Max And Aggregate?
Apr 6, 2011I've seen the operator Max used two different ways. The following two queries produce the same results. The MSIL code is slightly different. What is the benefit of using the Aggregate operator? Logically, is there a difference?
[Code]...
LINQ Expression Starting With From And Aggregate
May 4, 2012[code]The LINQ query going into "result" is incomplete because I'm stumped at what is happening there. I expect FirstOrDefault to refer to refer to a single Child object at that point in the expression, but it refers to a collection of child objects. Why? What is the best way to get a list of parents that have no associated child or have a child fitting a particular condition? (My actual code will have one child max, so this sample code is not representative.)I just don't understand what the collection of FirstOrDefault could be referring to. It should really be just 1 value or nothing at all times.
View 1 RepliesIDE :: Aggregate Query With Condition ?
May 28, 2012i have 1 query with aggregate with condition using access
SELECT
HD.No,
SUM(IIf(DT.Status = 'A', 1, 0)) AS One,[code]...
i can run it in access but not in .Net QueryBuilder
Run Aggregate Query Against A Datatable?
Mar 9, 2010I do have a datatable filled from a flatfile (say .txt). After the datatable is populated, I manipulate the data. After datamanipulation I want to do some analysis of the content of the datatable. For this analysis, I need to aggregate the data.
To keep it simple, let's say I have single orders of customers in the datatable and now I want to get the total order amount for each customer (SQL: SELECT Customer, Count(Ordervalue) FROM Ordertable GROUP BY Customer).
Linq To Sql, Aggregate Columns ,group By Date Into Listview?
Aug 3, 2011also i have a table called Orders with several columns, they contain information about the order and store the ID of the Customer they belong to.Question is: how can i query that table with (preferably) linq (the datacontext is from LinqToSql) to return the following dataI want to search for any entry with the matching CustomerID which took place, group them by Year, Sum the Totals respectively and add them to the listview?I now i could use lamda expressions and aggregate, its just not clear how (option infer on,db is a datacontext object,CustomerID is an int32 variable):
Dim Orders = (From order In db.Orders Where order.CustomerID = CustomerID).GroupBy(Function(p) p.Date.Year).GetEnumerator
I reckon i'd have to create an anonymous type like the following:
[code].....
Linq-to-Entities Aggregate A Column That Is Calculated In An External Function?
Feb 21, 2011I want to Sum of the calculated column Red which is calculated in the Function IsRed() that return an integer.When I run the query I get the following error: Method 'Int32 IsRed(Int32)' has no supported translation to SQL.How should I rewrite this to get it to work.
[Code]...
MS CRM 2011 + FetchXML - Aggregate Query Limit Exceeded?
Apr 12, 2012I've got a 2 part question: First question: I know that there is a query limit of 5,000 records when querying records, for performance reasons, but is there a limit when doing an aggregate query? If so, what is it? I need only to query the number of records and for one entity I can get a result of 39,000+ records, but for another entity I see an error message like:
[Code]...
Join An Array Of Strings?
Dec 25, 2009What's the easiest way to join an array of strings?
View 3 RepliesError : "You Have Tried To Execute A Query That Does Not Include The Spec Ified Expression 'ID' As A Part Of The Aggregate Function
Jul 1, 2009I am working on an application in Visual Basic Express using an Access 2000 database.I am trying to get athe following SQL query to work but get an error in Access.
The query is:
SELECT [ID], [Title], [Author], [Series], [YearPublished], [ISBN], [CoverPrice], [Style], [Condition], [Signed], [Comments], Count([Title]) AS CountOfBooks, Sum([CoverPrice]) AS TotalCoverPrice
FROM tblBooks
WHERE ID=[@ID];
The error is:"You have tried to execute a query that does not include the spec ified expression 'ID' as a part of the aggragate function."
The query works fine without the 'Count' and 'CoverPrice' count and sum to the query.
[String].Join To Concatenate HTML Strings
Mar 21, 2009Is there a way to use [string].Join to concatenate html stings including spaces? I want to join html strings with a special character so I can pass them as an array to javascript from the code behind.
Dim Array(100)
As
String
ArrayString = [String].Join(",", Array)
This works great if each item in Array is one word only, but what if each item in Array contains spaces?
LINQ Query Is Enumerated - Turn On Some Kind Of Flag That Alerts Me Each Time A LINQ Query Is Enumerated?
Sep 22, 2009I know that LINQ queries are deferred and only executed when the query is enumerated, but I'm having trouble figuring out exactly when that happens.Certainly in a For Each loop, the query would be enumerated.What's the rule of thumb to follow? I don't want to accidentally enumerate over my query twice if it's a huge result.
For example, does System.Linq.Enumerable.First enumerate over the whole query? I ask for performance reasons. I want to pass a LINQ result set to an ASP.NET MVC view, and I also want to pass the First element separately. Enumerating over the results twice would be painful.It would be great to turn on some kind of flag that alerts me each time a LINQ query is enumerated. That way I could catch scenarios when I accidentally enumerate twice.
Asp.net - Inner Join Using Linq .net?
Apr 15, 2011I'm trying to join two datatables of same keyfields.
table1
ID Class
---- -----
1 10
2 9
[code]....
Result
ID Class1 Class2
1 10 8
2 9 7
Inner Join In Linq To Entities?
Apr 23, 2009Given two tables, customer and orders, how would one do a linq query to entities to find any customers with open invoices started before a certain date?
View 1 RepliesJoin Datatable Using LINQ?
Jan 13, 2011I have two DataTables:
dt1 - personid, name
dt2 - personid
I want to create a third datatable to include records from dt1 when they are NOT in dt2 using LINQ. In this case, I can bind the third datatable to a dropdownlist.
Linq Join On Mutliple Properties
Jun 16, 2009Are the below two queries functionally the same?The first one doesn't return any data, but the second works fine with same exact input.Can someone point out what's wrong in my first query? [code]
View 2 RepliesC# - Join The Objects In A LINQ Expression?
Dec 15, 2011How to Join the objects in a LINQ select in this sample (C# variants accepted as well):
Class Room
Public Area As Integer
End Class
Class RoomPair
[code]....
C# - Linq To Objects Outer Join?
Mar 10, 2010Im trying to use LINQ to query Objets. I'm doing the following:
Dim myList As Generic.List(Of MyItem) = (From ThisItem In LinqToSqlObject.Items _
Join Folder In LinqToSqlObject.Folders _
On Folder.Id Equals Item.Id _
[Code]....
This however will not return any item that has a ParentItemId of null. I am trying to do a left join so as to return all "Item" regardless of whether it has a parent or not. I am aware that this is feasible in C# by adding an "into X" on the join, and selecting from X.DefaultIfEmpty(), this however does not appear to work in VB.Net.
Join With Multiple (OR) Conditions In LINQ To SQL?
Jul 8, 2010How would I perform this SQL query
Select Distinct s.*
from #ScopeIDs x
Join Scopes s on s.ScopeID=x.ScopeID or x.ScopeID is null
in LINQ to SQL? (This query would return all Scopes whose ScopeID is present in #ScopeIDs, unless one of the entries in #ScopeIDs is null, in which case it returns all Scopes).
A "literal" translation doesn't work, since LINQ doesn't support joining with multiple conditions - code along these lines ...
From x in ScopeIDs
Join s in Scopes on s.ScopeID equals x.ScopeID or x.ScopeID equals nothing
Distinct Select s
... doesn't compile.
Left Join Linq To Entity's .net?
Apr 11, 2011I can't figure out that linq to entity query syntax. My problem is that if the value of the Calls table is null then noting comes up, I want to make something like a left join to get 'all' rows from the Calls table.I tried to group it but I can't figure out the correct way to write it.
Dim TicketQuery As ObjectQuery = From c In EnData.Customer _
Join t In EnData.Calls On t.CustomerID Equals c.CustomerID _
Join Status In EnData.Lists On t.Status Equals Status.ListValue _
[code].....