Group Joins With Multiple Tables Converting SQL To Linq
Mar 7, 2011Can anyone help me by translating this SQL Statement to Linq? [code]
View 1 Replies
ADVERTISEMENT
Using Linq Group Joins In .Net?
Oct 18, 2011I'm trying to figure out how to use Group Joins in LINQ queries under VB.net. For some reason, every example I seem to find on the syntax is just plain At least, that's what my compiler keeps telling me. This is a simple example where I want to join orders to their order items so that I end up with a type that contains a collection of order items grouped together by their orderId's:
Dim groupedOrders = (From o In orders
Group Join i In orderItems On o.OrderId Equals a.OrderId Into myOrders
Select o.OrderId, myOrders).ToList()
What I'm currently running into in this example is that the 'myOrders' group I'm creating errors out with: Definition of method 'myOrders' is not accessible in this context.
Can't Use A Where Clause On Both Tables In A LINQ Group Join?
Jun 7, 2011Here'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?
Sql - Group By Having And Count As LINQ Query With Multiply Nested Tables?
Feb 28, 2012I 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.
Linq - Group By Multiple Columns?
Dec 6, 2011I 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]...
LINQ Group By With Multiple Properties?
Jun 10, 2011I spent a lot of time on this problem. I am able to do simple Group By LINQ queries (on one property) but for multiple fields I'm a little stuck... Here is a LINQPad sample of what I want to do :
dim lFinal={new with {.Year=2010, .Month=6, .Value1=0, .Value2=0},
new with {.Year=2010, .Month=6, .Value1=2, .Value2=1},
new with {.Year=2010, .Month=7, .Value1=3, .Value2=4},
[Code]....
Linq Group By And Order By Multiple Conditions?
Jan 16, 2012I have the following:
myFilteredContractors = (From c In myFilteredContractors
Join cc In myConClasses On c.ContractorId Equals cc.ContractorId
Where inClassifications.Contains(cc.ClassificationId)[code].....
This is properly ordering this list of contractors by the number of classifications that they have.I also want to order them by whether or not they have a field set (CompanyOverview), which if is an empty string should be below those contractors who have set their CompanyOverview. Also, after the CompanyOverview is ordered I want to order by Registration Date.So it should order by:
Number of Classifications
Whether Overview has been set (c.CompanyOverview)
Registration Date (c.AppliedDate)
Is it possible to all of this in LINQ?
Linq Group On Multiple Fields - Anonymous - Key
Apr 3, 2012I am stumped. I have a DTO object with duplicates patient address data. I need to get only the unique addresses.
[Code]...
.net - How To PLINQ An Existing LINQ Query With Joins
Sep 28, 2011I'm using LINQ to compare two DataSets with each other to create new rows and update existing. I've noticed that the complete comparison lasts ~1,5 hours and only one of the two cores is busy(Task-Manager is 50-52% CPU Usage). I assume that it could increase performance significantly.
how and what should I parallelize?
These are the original queries(reduced to the essentials):
'check for new data
Dim srcUnique = From row In src.Email_Total
Select Ticket_ID = row.ticket_id, Interaction = row.interaction, ModifiedAt = row.modified_time
[Code].....
Optimize Linq To SQL Query, Group By Multiple Fields
Jul 26, 2010My 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."
LINQ: "Group Join" Functionality Joining Three Tables
Jul 21, 2010I'm working on becoming familiar with LINQ, and getting my head around the syntax and usage.
[Code]...
C# - LINQ To SQL Join 3 Tables And Select Multiple Columns And Also Using Sum
Aug 19, 2010I have three tables Student, TimeSheet and TimeRecord.
Talbe columns:
Student : StudentId, FirstName,
LastName
TimeSheet: TimeSheetId,StudentId, IsActive
[Code].....
Iterate Through LINQ Query - Results From Multiple Tables
Dec 7, 2010I 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?
.net - Multiple INNER JOINs SQL Server Compact Edition?
Feb 21, 2012I am trying to do multiple joins within a single query in SQL Server CE. I know that SQL Server CE doesn't support multiple SELECTs but I can't find any information on multiple INNER JOINs. I keep getting token errors on ProjectItemMaster (after the FROM) and the first INNER JOIN. Warning this is a big ugly query used to generate reports.
[Code]...
If I can't uses multiple INNER JOINs I can I break the query down into a number of different queries but I would prefer not to. So it is easier to maintain a SQL Server CE and an SQL Server version of the program.
Datagrid And Queries With Multiple Joins To Update A Table
Jan 19, 2010We use an ERP program in conjuction with SQL server and lots of tables that keep track of product records customers and all these kinds of stuff. I am building a fairly simple Warehouse Inventory Application that warehouse employees will use to count and inventory stock on hand from products around the warehouse and then check if any quantity errors occur between stock on hand and logistic quantities.I have created a custom Inventory table that I would populate with records like {stock_code, Description, Quantity_remain, quantity_avail}. Those records already exist in 2 different tables in my database. The aim is that I want my program to fetch daily 10 random codes from the stck table, and records about their quantities,bringing together the "columns" that my new custom table has +2 more editable columns: inventory, date, user with a query like this:[code]
As you can imagine my custom table contains the exact same fields as the query suggests + 3 more.I use the the stck table to fetch product info like its code,description, its group_code, categ_code, and then I outer join the stck_x_th that contains records concerning quantities remaining or available asociated with each product code and the "places" those quantities reside in the warehouse.Then, store them in a datagridview control, with only one editable column -inventory- (user and date columns are auto-populated with default values), and then when the user fills out the inventory quantites, I want to save the datagrid "as it is" in my new custom table.I am creating a databound gridview control using one table adapter (which refers to the inventory table). Then I add to my table adapter using "add query" the forementioned query with a fillby method to populate the Datagrid (and therefore my Table in SQL) with the exact same values my query returned from the different tables. But whenever I try to update the table concurrency exceptions raise.It works like a charm if I enter the values in the grid cells manually, without populating the grid control with the new fillby method my self but not if I populate the grid via a query and then hit update.
LINQ Group By Multiple Values Does Not Work Well But Work Well In C#
May 29, 2012I found that when group by multiple values does not work well with VB.NET, but it works well with C# ,here are my code, is there something wrong with my VB.NET Code? Here is my VB.NET code:
[Code]...
VS 2008 DataAdapter.Update To DataTables With Multiple Base Tables (Joined Tables)?
Jul 12, 2011have a datagridview containing 2 tables left joined, so that:table1 LEFT JOIN table 2 ON table1.id=table2.idI get an error whenever I try to edit my datagridview."invalidOperationException was unhandled by the user codeDynamic SQL generation is not supported against multiple base tables."The error points to this line:
da.FillSchema(dt, SchemaType.Mapped)
da.Update(dt) << This line
'da = dataadapter
[code].....
Asp.net - Linq To SQL: Group By And Sum()?
Aug 9, 2010I have the following LINQ statement, which performs a simple linq query and assigns the resulting values labels on an asp.net web form:
Dim db As New MeetingManagerDataContext
Dim q = From s In db.vwRoomAvailabilities _
Where s.MeetingID = lblMeetingID.Text _
[code]....
Originally, there was going to be only a single row result and you can see I'm using FirstOrDefault() to grab that single value which works great. But the design has changed, and multiple rows can now be returned by the query. I need to now Group By the MeetingID above, and SUM each of the selected columns (i.e. s.AllRequestedDoubles).
LINQ Group By SUM
May 28, 2012[code]how can i group by SUM of the c.ct?
View 1 Replies.NET LINQ Group Joining?
Dec 2, 2010I have a list of object with 3 properties (quantity, service, name)I want to have all names and services in my list (kinda cross join) and the grouped quantity of the row corresponding. Not clear i guess
Quantity Service Name
3 Srv2 Bob
4 Srv2 Paul
2 Srv1 Paul
1 Srv2 Nick
I want as output All the services and all the names and corresponding quantities (0 if none)
Srv1 Paul 2
Srv1 Bob 0
Srv1 Nick 0
[code]....
Here is what I got so far, but I dont even get the expected resultsAnd I am acutally certain there is a pretty easy way of achieving what i want...
Dim services = (From a In interventions Select New With {.Service = a.Service}).Distinct()
Dim months = (From b In interventions Select New With {.Month = b.DateHeureIntervention.Month}).Distinct()
'Dim query = (From s In services _
[code]....
C# - LINQ To SQL Group People By Age?
Nov 3, 2011Sample in C# and VB.NET are OK.
I have a table "People" with the following columns:
-FullName (nvarchar not null)
-DOB (datetime null)
I want to write a LINQ to SQL to group the people by age, like following result:
Age 19: 4 ppl
Age 20: 5 ppl
Age 21: 6 ppl
Here's my try:
Dim query = From ppl In db.People _
Select New With {.Age = DateTime.Now.Year - CDate(ppl.DOB).Year, .CountAge = ppl.Count}
Notice that there are no DOB record for some people in the tables, so these shall not be included. The DOB column has record like this 1982-10-24 10:12:45 AM because it's a DateTime column.
C# - Using LINQ To SQL Group, Sum And Aggreate All Together
Aug 18, 2010I have two tables Students and Origami. Origami has Foreign Key of Students table. Each student can make one or more origami for each month.
[code]...
This query give only total origami of each student. But I want monthly count of origami for each student.
Linq InvalidCastException For Group By
Dec 12, 2010I have a Linq Group By query that works. Here's the query:
Dim query = From fb As Feedback In lst Where fb.Seller.login_name.ToLower = UserName.ToLower
Order By fb.transaction_id Descending, fb.creation Descending _
[Code]...
I don't like working with anonymous types so I'm trying to delare the result and am hitting an InvalidCastException.
Here's the type information:
Debug.Print(query.GetType.ToString)
Returns:
System.Linq.GroupedEnumerable`4[Feedback,System.Nullable`1[System.Int32],Feedback,VB$AnonymousType_0`2[System.Nullable`1[System.Int32],System.Collections.Generic.IEnumerable`1[Feedback]]]
[Code].....
Linq InvalidCastException For Group By?
Aug 12, 2011I have a Linq Group By query that works. Here's the query:
Dim query = From fb As Feedback In lst Where fb.Seller.login_name.ToLower = UserName.ToLower
Order By fb.transaction_id Descending, fb.creation Descending _
Group fb By fb.transaction_id _
Into Group
I don't like working with anonymous types so I'm trying to delare the result and am hitting an InvalidCastException.
Here's the type information:
Debug.Print(query.GetType.ToString)
Returns:
System.Linq.GroupedEnumerable`4[Feedback,System.Nullable`1[System.Int32],Feedback,VB$AnonymousType_0`2[System.Nullable`1[System.Int32],System.Collections.Generic.IEnumerable`1[Feedback]]]
and the inner type:
Debug.Print(item.GetType.ToString)
Returns:
VB$AnonymousType_0`2[System.Nullable`1[System.Int32],System.Collections.Generic.IEnumerable`1[Feedback]]
So, armed with this information, here's the declaration used:
Dim query As IEnumerable(Of IGrouping(Of Int32?, IEnumerable(Of Feedback)))
Here's the error returned:
Unable to cast object of type 'System.Linq.GroupedEnumerable`4[Feedback,System.Nullable`1[System.Int32],Feedback,VB$AnonymousType_0`2[System.Nullable`1[System.Int32],System.Collections.Generic.IEnumerable`1[Feedback]]]' to type
[code]....
VS 2010 LINQ To SQL Group By?
Nov 16, 2011In proper sql my query would be something like:
select c.id, c.name, count(i.id)
from cats c, items i
where c.id=i.catid
[code].....
SQL Performance Timeouts - Adding Multiple Rows In Multiple Tables In The Database
Sep 12, 2011I have a vb.net web application and when a particular function runs , i get data timeouts in the rest of the application..(ie..row not found errors or column does not belong to table but it does) The function is adding multiple rows in multiple tables in the database and is running in a for loop. It seems to be all SQL related but I am not seeing anything in the error logs in SQL or in the application Right now I am assuming it is memory related where to start note..the for loop will be replaced with a bulk insert but right now I jest need to resolve the issue of the timeouts
View 1 RepliesLinq Doing A Group In A Lambda Subselect
Feb 23, 2012I have a user input screen that allows them to select input values and the resulting query is dependent on which values the user entered. The resulting query groups up the results to present a list of unique customers
Simplified table design - A customer can be in many states
[code...]
C# - Pick Out A Group Of Items Using Linq To Xml
Jul 4, 2011My head is fuzzled with this. I have an xml doc which has the layout for a grid stored in it. If you notice the columns are stored as "Items" in the XML. I am trying to retrieve each "Item" out of the XML using LINQ but no matter what I do I keep taking on straggler properties that I don't need.
[Code]...
Get A Linq To Sql Group By Query Into The Asp.net Mvc View?
Dec 23, 2009I 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.
Linq Group Count Items?
Dec 5, 2011I 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