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
ADVERTISEMENT
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
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
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
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
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
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
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
Nov 8, 2010
I solved this issue in this thread but there still are issues with special chars. ie 'I thought by doing parameters that it would take care of any special chars for you. Well i am still getting mysql exceptions about syntax. I look at the string it is working with and it has a "'" in it.Why are they not working as I expect them to?
View 7 Replies
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
Aug 6, 2009
if there is that much of a performance gain in running a LINQ stored procedure versus a LINQ query?
View 1 Replies
Nov 30, 2011
The data was being read from a Gridview which had the html encoding. The solution was the following...
Dim Sht_Text As String = HttpUtility.HtmlDecode(row.Cells.Item(0).Text)
I am trying to prevent from having to escape apostrophes and other special characters in my string variables by using a parameterized query with an OleDbConnection. I have all of the parameters in the same order, the insert is working, it just does not maintain the characters. I keep getting those pesky html codes. I am trying to insert into an Access db from a web pages. As I mentioned, its working, just not the special characters.
Here's the code:
Dim pConn As New OleDb.OleDbConnection pConn = New OleDbConnection(cb.ConnectionString)
Dim SqlString As String = "INSERT INTO Strings (Mlt_String_ID, Lng_ID, Strg_Name, Sht_Text, Lng_Text, Alt_Text) Values (?, ?, ?, ?, ?, ?)"
[code]....
jp and en records already existed, the insert of fr record pushes the html codes for the small circle which represents the degree symbol. Happens for all other special characters as well.
View 1 Replies
Sep 14, 2010
I have to join two main tables, and I need to filter the results by elements in an ASP.NET web form. These filters are created on the fly so I have to use a lot of where extensions to filter the query. I want to execute the query with as optimized SQL as possible.
I am first doing a simple join between TW_Sites and TW_Investigators. Then there are two sub-tables that are involved. TW_InvestigatorToArea and TW_InvestigatorToDisease. While most of the where clauses are working fine, I have found a performance issue that won't be an issue right now, but will be an issue as the table gets bigger.
The arrays DiseaseCategories and DiseaseAreas would be the results of a CheckBoxList result.
Protected Sub LoadResults()
'Get Dictionary of Filters
Dim FilterDictionary As OrderedDictionary = Session.Item("InvestigatorFilterDictionary")
' Initialize LinqToSql
[code]....
View 2 Replies
Jun 29, 2010
I'm trying to use a nested multi-line lambda Function in VB.NET and am getting an error. Here's what my code looks like:
cartItems = cartItems.Select(Function(ci) New With {.CartItem = ci, .Discount = discountItems.FirstOrDefault(Function(di) di.SKU = ci.SKU)})
.Select(Function(k)
If k.Discount Is Not Nothing Then
[code]....
View 2 Replies
Mar 31, 2011
Forgive my ignorance on this.I have this LINQ Query:Dim ngBikersDataContext As New CarBikeWalkDataContext
bikersList = (From c In ngBikersDataContext.Reg_Bikers _
Order By c.L_Name _
Select New Bikers() With { _
.BikerID = c.BikerID, _
.F_Name = c.F_Name, _
[Code]...
with the error "Overload resolution failed because no accesible 'Select' accepts this number of arguments."
Over the "NEW" I get an error " ')'expected."
View 1 Replies
Jan 27, 2011
We are doing a query against an in-memory collection of LINQ data objects. The wrinkle is that we are ordering by a column in a related table whose records have not necessarily been loaded yet (deferred loading:)
Dim oPkgProducts = _
From b In oBillPkg.BillProducts _
Where b.Successful.GetValueOrDefault(Common.X_INDETERMINATE) = _
[code]....
View 1 Replies
Aug 31, 2011
How do I add ROW_NUMBER to a LINQ query or Entity? How can I convert this solution to VB.NET?
[Code]...
I'm having trouble porting that last line. I have been unable to locate a VB.NET example. I'm actually not looking for any paging functionality like the example provides, just good old-fashioned Row_Number(Order By X) row index.
View 1 Replies
Apr 16, 2011
I was wandering is it possible to use a query within another query below is the code I am trying to use.
Public Function GetInventByComp(ByVal CompID As String) Using DC As New DataClassDataContext
'need to get company id's based off names? thats bad should be name from Id need to rethink this
Dim invent = (From C In DC.Inventors_Companies _
Where C.CompID = CompID _
Select C.InventorID).ToString
[Code]...
I was trying to us multiple values in a string like "1, 3, 5" but I can't seem to get that working either so I am trying to use just a single value now. Can anyone help me? "Yes I am new to this"
View 5 Replies
Jan 23, 2012
I have two classes, one nested in the other. [code]Neither "Name" or "ID" are unique between operations and records.I wish to construct a dictionary using LINQ = Dictionary(Of String, Of List(Of Integer), whereby the keys are uniqe examples of Names in my collection and the values are the collective set of distinct IDs that are associated with those names.
View 2 Replies
Mar 4, 2009
i need perform the following query to save into db the problem is sometimes the user enter some characteres that interfer with the sql command for example
if the user enter this word: 'hello'when i try save this result i error because the final query is this:
insert into x (v) values (''hello'') and for update this problem ocorr to how can i save the value ' to the db replacing the ' charater for other?
View 1 Replies
Sep 4, 2009
I've been reading a fair bit about the performance of using LINQ rather than using a for each loop and from what I understand using a LINQ query would be a little bit slower but generally worth it for convenience and expressiveness. However I am a bit confused about how much slower it is if you were to use the results of the query in a for loop.
Let's say that I have a set called 'Locations' and a set of objects called 'Items'. Each 'item' can only belong to one 'location'. I want to link items that are under the same location to each other. If I were to do this using a normal 'For Each' loop it would be something like this:
For Each it as Item in Items
If it.Location.equals(Me.Location)
Me.LinkedItems.Add(it)
End If
Next
However if i was to use LINQ it would instead be this:
For Each it as Item in Items.Where(Function(i) i.Location.equals(Me.Location))
Me.LinkedItems.Add(it)
Next
is the second (LINQ) option going to loop once through the entire 'Items' set to complete the query, then loop through the results to add them to the list, resulting in essentially two loops, or will it do the one loop like the first (For Each) option?
View 2 Replies
Mar 24, 2012
Is it possible to do the following? [code] Basically I have one Load_Gridview function that is called on all postbacks, and rather than creating a bunch of different cases.I want the filters to stack.My actual code has more filters set up (4 or 5 of the).It all compiles ok but when I run and try to execute with active checked, or a department selected I get the following error. [code]
View 4 Replies
May 5, 2009
I have below XML file
<?
xml version="1.0" encoding="utf-8" ?><
PageFlow>
<
attributegroup name="Search"><
attributes>
[Code]...
View 2 Replies
Apr 18, 2012
I am looking to sum the "Status" field.
Dim _detailRecords As New DataTable
_detailRecords.Columns.Add("y")
_detailRecords.Columns.Add("z")
[Code]....
I'm also wanting to keep this as an IEnumerable of DataRow because I will be creating a datatable from it with
Dim yyyyy As DataTable = query8.CopyToDataTable
View 1 Replies
Aug 13, 2010
I am Fairly new to Linq and I am trying to write a simple query to return the error messages within my xml file.
<?xml version="1.0" encoding="utf-8"?>
<Error xmlns="urn:xxxxx">
The following errors were detected:
[code].....
View 1 Replies
Aug 26, 2009
if I run this under c#
from p in Addresses where p.Address2 == null select p.AddressID
it generate this query
SELECT [t0].[AddressID]
FROM [dbo].[Address] AS [t0]
WHERE [t0].[Address2] IS NULL
if I run this under vb.net
from p in Addresses where p.Address2 = nothing select p.AddressID
it generate this query
SELECT [t0].[AddressID]
FROM [dbo].[Address] AS [t0]
WHERE [t0].[Address2] = ''
p.Address2 is a varchar field that accept null value
why vb.net is "wrong" ?
View 1 Replies
Dec 1, 2009
I'm converting the Linq query below from C# to VB.Net. Can you spot my error? The query joins 3 XML datasets. C# - This one works great.
List<Course> courses =
(from course in CourseXML.Descendants(ns + "row")
join coursecategory in CourseCategoryXML.Descendants("Table") on (string)course.Attribute("code") equals (string)coursecategory.Element("DATA")
[code]....
View 2 Replies
Mar 14, 2011
I have been trying the following but it returns unexpected results:
Dim xd As XDocument = _
<?xml version="1.0" encoding="utf-8"?>
<root>
[code].....
The above result returns both 'element' nodes however it should only return the first where element/subelement@id=1/subsubelement@id=3 However if results.Ancestors. is used it returns the correct 'subelement' and if that line is not included it returns a single 'subsubelement' whih is also correct I don't understand why when mvoing to the 'element' it returns both (I realise both have a subelement with id=1 but I thought each further query would filter out the presvious results)?
View 1 Replies
Feb 22, 2012
I have a List of Price Objects (Price contains Date, High, Low) and am trying to extract monthly averages via LINQ. I have this working in C# but we have a few legacy applications that need it in VB.Net and I can't quite seem to figure out the conversion. I've even tried breaking it into two queries to no avail.
[Code]...
View 2 Replies
Jun 24, 2011
Ok im trying to do a if statement in Linq and was wondering if it was possible to do something like:
Dim loadFriends = From p In db.UserRelationships Where p.aspnet_User.UserName = User.Identity.Name _
Or p.aspnet_User1.UserName = User.Identity.Name And p.Type = 1 _
Select New With {if p.aspnet_user1.user = "a certan username" then .username = _
p.aspnet_user.username else .username = p.aspnet_user1.Username}
[Code]...
View 1 Replies
