Sql - Group By Having And Count As LINQ Query With Multiply Nested Tables?

Feb 28, 2012

I have the following SQL query to return all Customers who have no OrderLines with no Parts assigned - i.e. I only want the customers within which every order line of every order has no parts assigned - (in the actual problem I am dealing with a different domain but have translated to customers/orders to illustrate the problem)

SELECT c.Customer_PK
FROM Customers c
INNER JOIN Orders o[code].....

This works but generates less than optimal SQL - it is doing a subquery for Count on each row of the customers query rather than using Group By and Having. I tried making the LINQ Group By syntax work but it kept putting the filter as a WHERE not a HAVING clause.Edit in response to Answers below: I am accepting JamieSee's answer as it addresses the stated problem, even though it does not produce the GROUP BY HAVING query I originally had.I am a VB developer so I have had a crack translating your code to VB, this is the closest I got to but it does not quite produce the desired output:

Dim qry = From c In context.Customers
Group Join o In context.Orders On c.Customer_PK Equals o.Customer_FK
Into joinedOrders = Group[cod].....

The problem I had is that I have to push "jl" into the Group By "Key" so I can reference it from the Where clause, otherwise the compiler cannot see that variable or any of the other variables appearing before the Group By clause.With the filter as specified I get all customers where at least one order has lines with no parts rather than only customers with no parts in any order.

View 3 Replies


ADVERTISEMENT

LINQ Query To Group Data Between Two List Collections, Populating A Nested Object

Oct 26, 2011

I have these objects:[code]Using LINQ I need to take a List(Of MakeInfo) and a List(Of ModelInfo) and aggregate the StockInfo from ModelInfo into the List(Of MakeInfo).So for each MakeInfo I will have a total count of all stock where MakeInfo.Name = ModelInfo.Make, and also a minimum price.I think it's going to be something like this, but I'm having trouble accessing the nested object and not sure if it's going to be possible with a single query.[code]

View 2 Replies

Linq Group Count Items?

Dec 5, 2011

I have this linq to entity

From r In ReceiptRepository.Fetch
Where
r.RECEIPTDATE >= ReportStartDate And

[Code]....

This is working fine, except the count property, it is just giving the number of group count. I don't know how to find out each Tender Count

View 1 Replies

LINQ To SQL Using GROUP BY And COUNT(DISTINCT)

Jan 15, 2009

I have to perform the following SQL query: select answer_nbr, count(distinct user_nbr)
from tpoll_answer where poll_nbr = 16 group by answer_nbr The LINQ to SQL query

[Code]...

View 5 Replies

Special Nested Query In LINQ?

Nov 30, 2011

I'm writting a graph algorithm and I'm almost there...My algorithm stores several edge objects in a collection of edges, representing a path from initial vertex to final vertex.hus, there should be also several paths between those vertexes (several collections of edge type), stored in another collection (in my class, callded "collswap").Each edge is an object which have two vertexes: initial (v) and final (w), in this way:

dim manhattan as new vertex
dim brooklyn as new vertex
dim statenIsland as new vertex

[code].....

View 1 Replies

Can't Use A Where Clause On Both Tables In A LINQ Group Join?

Jun 7, 2011

Here's what I want to do:

Dim queryX = From m In db.Master
Where m.Field = value
Group Join d In db.Detail
On m.Id Equals d.MasterId Into Group
Where d.Field = value

In English, I want to join the master and detail tables, specifying conditions on each. But this causes the following compiler error:

"Name 'd' is either not declared or not in the current scope."

It works if I put this same condition in functional form:

Group Join d In db.Detail.Where(Function(x) x.Field = value)

but I think this is more typing, harder to understand, and introduces that irritating dummy variable. I really would prefer to use the query comprehension syntax. Is there a way to accomplish this?

View 1 Replies

Group Joins With Multiple Tables Converting SQL To Linq

Mar 7, 2011

Can anyone help me by translating this SQL Statement to Linq? [code]

View 1 Replies

Get A Linq To Sql Group By Query Into The Asp.net Mvc View?

Dec 23, 2009

I have the following query that groups parking spaces by their garage, but I can't figure out how to iterate the data in the view.

Public Function FindAllSpaces() Implements ISpaceRepository.FindAllSpaces
Dim query = _
From s In db.spaces _
Order By s.name Ascending _

[code]....

The controller is taking the query object as is and putting it into the viewdata.model and as stated the view is not currently strongly typed as I haven't been able to figure out how to do this. I have run the query successfully in linqpad.

View 2 Replies

How To Group Distinct Columns In A Linq Query

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

Order By And Group By In A Linq To Objects Query?

Sep 28, 2010

How do I order by and group by in a Linq query?I tried..Dim iPerson = From lqPersons In objPersons Where Len(lqPersons.Person) > 0 Group lqPersons By key = lqPersons.Name Into Group Order By Group descending Select Group, key

For Each i In iPerson
tmp = tmp & vbNewLine & i.key & ", " & i.Group.Count
Next

[code].....

View 5 Replies

LINQ Query With Sum And Group By Is Adding Decimal Precision

Aug 24, 2009

I am trying to do a simple summary of table data using a LINQ query.a regular SQL Query looks like:[code]This query works fine and returns the correct results.[code]This LINQ query returns the incorrect results some of the time. Off by .01 in most cases.The "value" column is of type Numeric (12,2) in the database.Now when I show the values from the Sum in a msgbox I get extra precision and thus incorrect results as my end result because of rounding.For example I get 835.18 + 54.62 = 889.80960 from the above LINQ query.I am running Visual Studio 2008 9.0.30729.1 SP on Vista 64Bit with .NET 3.5 SP1.

View 3 Replies

Optimize Linq To SQL Query, Group By Multiple Fields

Jul 26, 2010

My LINQ query contains the following Group By statement:

Group p By Key = New With { _
.Latitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Latitude, _
.Longitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Longitude}

The query works, but here is the SQL that the clause above produces

SELECT [t6].[Latitude]
FROM (
SELECT TOP (1) [t5].[Latitude]

[Code]....

but this produced an error: "A group by expression can only contain non-constant scalars that are comparable by the server."

View 1 Replies

Get The Count From A Query Expression In Linq?

Jan 25, 2012

What I have right now is this:

Dim users = From users In tempTable _
Distinct Select users.Item("s_userid")
Dim usersCount As Integer = users.Count

But I pretty sure I shouldn't have to do that. I should be able to get the count in/from that first linq query.

View 2 Replies

C# - LINQ To SQL To Query Parent And Count Childs In Hierarchy?

Dec 8, 2010

I'd take both C# and VB.NET suggestion.I'm using LINQ to query data. I'm trying to query the parent and count the child tags.Here's my Tags table column:

TagId (int primary)
TagName
ParentId (int Allow NULL referred to TagId column)

Here's some sample data:

[Code]...

View 3 Replies

LINQ - Query Children Object If Parent Count Is = 1

Apr 17, 2012

I have a the following Class structure. Company > List(of Departments) > List(of Employees) I want to Query a Company to find out if it has a department of the following name and a Employee in that department with the following ID! How could I query this. The way I have the code it expect to return one department so I have it doing

[Code]...

View 2 Replies

Iterate Through LINQ Query - Results From Multiple Tables

Dec 7, 2010

I have a VB .Net linq query that I want to return values from two tables. I currently have this:
Dim query = (From c In db.Client _
Join u In db.Users _
On u.UserID Equals c.UserID _
Where (c.ClientID = 1)
Select c, u).ToList
query (System.Collections.Generic.List(Of )) returns several rows as expected each item containing a c and a u.
How do I iterate through the queries collection?

View 1 Replies

LINQ Query - Join Tables On Nullable Columns

Apr 17, 2011

How 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

View 2 Replies

LINQ: "Group Join" Functionality Joining Three Tables

Jul 21, 2010

I'm working on becoming familiar with LINQ, and getting my head around the syntax and usage.

[Code]...

View 1 Replies

ASP.NET MVC - Linq Query With Count Returns Anonymous Type - How To Display In View

Sep 29, 2011

So I'm writing a query as follows:

Dim assSummary = From a In db.Assignments
Join ur In db.UserRegions
On a.Origin.ID Equals ur.Region.ID

[code]....

In the controller I can return the data easily as follows:

For Each c In assSummary
MsgBox(c.Description & " " & c.AssCount)
Next

If I pass the object through to the view using Viewdata("assSummary") = assSummary, how do I display the data? Every method I've tried results in messages about 'VB$AnonymousType_7(Of Integer,String) and I don't know how to retrieve the data from the anonymous type.

View 1 Replies

Multiply Textbox Value With Ms Access Tables Field Value?

Jun 17, 2011

I want to multiply textbox value with ms access tables field value and insert the output in field two .i get error : table is not declared.

Private Sub TextBox1_Change()
If IsNumeric(TextBox1.text) Then
table("table1").field2("record1").text = TextBox1.Text *

[Code].....

View 2 Replies

Asp.net - Group Values In A Nested Repeater?

Nov 29, 2011

I have a table of questions and a table of alternatives corresponding to each question. I'm planning to build a questionnaire system (in ASP.NET) which select a few random questions from that total of questions.

I'm using the following SQL Statement to choose the random questions:

SELECT TOP (@maxQuestions) QuestionID, QuestionName FROM Questions WHERE CourseID = @CourseID ORDER BY NEWID()

Which populates a DataTable in a DataSet and data bind it to a Repeater control (the parent Repeater). What I need now is to get every alternative related only to the current random questions generated from the query above in a separate SQL Statement so that I can populate the DataSet with a new DataTable, add the relation between them two and data bind it to another Repeater control (the child repeater).

I don't know how to get only the alternatives related to the current random questions I generated.Otherwise, I get an error saying that I don't have all child values corresponding to the parent values and I can't group the alternatives inside the same question.

View 1 Replies

LINQ Query Is Enumerated - Turn On Some Kind Of Flag That Alerts Me Each Time A LINQ Query Is Enumerated?

Sep 22, 2009

I 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.

View 3 Replies

.net - Linq Nested Grouping?

Jul 7, 2009

I've a large table of Items and I need to organize them by Category, then by Year and then by Month.Item has CategoryID and Dated properties.I got this far:

Dim Items = From Item In DB.Items _
Group By CategoryID = Item.CategoryID _
Into Categories = Group _
Order By CategoryID

But where I put the:

Group By Year = Year(Item.Dated)

and the

Group By Month = Month(Item.Dated)

The final result should be something like this:

For Each Category in Categories
For Each Year in Category.Years

[code].....

View 2 Replies

C# - How To Get Count Of Each Group In MS Reports

Jan 16, 2012

In MS Reports, I have a tablix(table) where I group on foo. How do I get the count for each group? This is how I would do it for the total count: =Count(Fields!foo.Value) , but it's not enough.

View 1 Replies

Group And Count Datatable?

Jan 20, 2011

I have the following DataTable:

Date
08/06/2008
09/06/2008
04/06/2008
08/06/2008

[Code].....

View 2 Replies

Nested SQL Query Within A While Loop In ASP.NET

Dec 2, 2011

I intended to do another SQL query inside here and retrieve data from another table by using the "category_id".I know the problems that asp.net required me to close the data reader before proceed to another query. But is there any solution for me to do another query and open another data reader within the opening data reader? [code]

View 2 Replies

C# - Linq - Find Element In Nested Collections

Feb 19, 2011

I have a generic list - SupportedTypeGroups. Each SupportedTypeGroup has SupportedTypes property (generic list of SupportedType). How to construct a Linq query to locate SupportedType with required name?

View 3 Replies

VS 2008 - Using Nested IF Statements In A SQL Query?

Jan 13, 2011

I have a question about using nested IF statements in a SQL query. I have a select statement that calculates a semester grade -- Dim sqlStr As String = "SELECT 'XXX-XX-' + right(socSecNumber,4) As [Student SSN], Round((firstExam + secondExam +(2*finalExam))/4, 0) AS [Semester Average] FROM Grades that I am displaying the results in a datagridview. I can successfully display the masked social security number and the Semester Average but I also need to convert the Semester Average to a letter grade to display in a newly created column for Grade. Can this somehow be done with nested If or Case statements within the SQL statement or is there a way to pass the Semester Average information as a variable to a nested vb If statement? Basically, I am needing to know how to display the letter grade to the datagridview using this criteria:

[code]...

View 16 Replies

Calculate The Group By Count Of Keys In A Dictionary?

Nov 10, 2010

I have the following dictionary:

Dim idQueuedJobs As IDictionary(Of Int32, KeyValuePair(Of String, Int32)) = New Dictionary(Of Int32, KeyValuePair(Of String, Int32))

Why duplicate KeyValue pair? This is because the first Int32 is just a normal index, and the String followed by another Int32 contains systemnames and the priority of the qued job in idQueuedJobs

I want to calculate the total number of systemnames in idQueuedJobs, how can i obtain this count by using the groupby method?

Something like this maybe?

numberOfSystems As Int32 = idQueuedJobs.Values.GroupBy(...)

View 1 Replies

.net - ActiveRecord Linq/NHibernate Linq Not Building Query Completely?

Jul 14, 2011

given this function:

Public Function Search(ByVal StartIndex As Integer, _
ByVal MaxResults As Integer, _
ByVal AccountNumber As String, _
ByVal LastName As String, _

[Code]....

instead of what I expected:

Select count(*) from remitline where lastname = "smith"

What am I doing wrong building up the where clause? I'm using Castle ActiveRecord 2.1

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved