SELECT EqID, MakeID, Model, Description,
(SELECT MAX(EvDateEnd) AS MaxOfDateEnd
FROM tblEvent WHERE (EqID = tblEquip.EqID) AND (Event = 'REG')) AS RegExpire,
[code]....
For example, I want to get the Max EvDateEnd from the Events table for each record in tblEquip, but not all records in tblEquip have the "REG" event, while others have multiple REG events.Some events (like EAE) I leave the tblEvent.EvDateEnd field blank to show the event is open and ongoing, such as issuing a vehicle to an employee(entity). when the vehicle is returned I enter the date in EvDateEnd essentially closing the event.
I'm trying to query old Access database tables and compare them with SQL Server tables.They often don't have primary keys, or they have extra fields that had some purpose in the nineties, etc., or the new tables have new fields, etc.I need to find records - based on a set of fields specified at runtime - that are in one table but not another.So, I do this kind of query all the time in SQL, when I'm comparing data in different tables:
dim fields_i_care_about as string = "field1, field2, field3" 'This kind of thing gets set by a caller, can be any number of fields, depends on the 'table dim s as string= ""
[code]....
It tells me it can't convert a Boolean - Is there any way to do this without Linq expressions? They seem far more complex than what I'm trying to do here, and they take a lot of code, and also I can't seem to find examples of Expressions where we're comparing two fields in a subquery.Is there a simpler way? I know I could do the usual EXISTS query using JOIN or IN - in this case I don't need the query to be super fast or anything. And I don't need to use a DataTable or DataSet - I can put the data in some other kind of object.
I've got a subquery that returns the most recent value from a child table. In some cases the subquery returns nothing. The query below fails at runtime because the inferred type of MemberPrice is decimal and is not nullable. [code]...
I am new to LINQ, how to convert the following SQL query to LINQ? SELECT Date,ShiftName,Max(Score) AS Score, 1 AS IsPreferred FROM Temp_Nurse_Fill_RequestNumbers group by ShiftName,date
I have the following routine (that works) but which is messy to update owing to the hand-typed strings it uses:
Private Sub ListDefaults() Dim conn As New SqlConnection( _ "server=bas047AUTODESKVAULT;Database=DWGDetails;Integrated Security=SSPI") 'Dim conn As New SqlConnection( _
I've created a small WPF app in VB9 to catalog our sizable DVD collection. This exercise has been more about learn programming than the need to have a movie database app, but I digress.The SQL database I created has a field for "Genre" that does not have an entry for all the movies in our collection. As such, when I run the method that filters the movie listing by "Genre" the compiler throws the "StrongTypingException was unhandled" error stating "The value for column 'Genre' in table 'Movies' is DBNull.".
I now understand where the DBNull problem comes from and have been searching for several hours to find a VB solution that converts the DBNull fields to strings on the fly during the LINQ query. I've found many solutions people have tried but, since I'm quite new to all this, none of them have worked for me. Pasted below is the method that throws the exception.
Is there a way to convert a string into a linq query? Searching for those words brings up tons of stuff unrelated to what I'm trying to do..For instance, this works, of course...
Dim lstMyQuery = (From item in htMyTable.values where item(0) = "Apple" Select item)
But somehow I want to do this:
Dim strQuery = "From item in htMyTable.values where item(0) = " & CHR(34) & "Apples" & CHR(34) & " Select item" Dim lstMyQuery = (Somehow run/convert strQuery as a query)
The reason is strQuery will be different each time depending on what the user has selected earlier in the program.R if lstMyQuery is already a collection, is there a way to add to it?
Dim lstMyQuery = (From item in htMyTable.values where item(0) = "Apples" Select item)
(this obviously doesn't work, but it's basically what I want to do...)
lstMyQuery.Add(From item in htMyTable.values where item(0) = "Oranges" Select item)
GOAL: Bind nested ListViews to LINQ generated iQueryable of anonymous type. I want to use LINQ because you can use GroupBy and bind the nested ListView to the 'it' keyword.
SETUP: I have groups of sets of conditions. Each set of conditions is stored in the BillingCodes table. Each group of BillingCodes is stored in the BillingGroups table.
I have a custom object that stores the ID, Name, and NumCodes for each BillingGroup that the user has chosen.I have a collection of these objects called GroupsList that has a list of the groups that the user has chosen.
Problem 1: I can iterate through GroupsList and grab all the IDs. How do I translate the SQL 'WHERE ID IN(a string of comma delineated IDs)' for LINQ to SQL? Is that the best way to do that?
Problem 2: Once I have the list of BillingGroups I need to iterate through each group. For each group, I need to iterate through the BillingCodes. For each BillingCode I need to generate a WHERE clause that has all of the conditions in the BillingCode. I propose something like so:
for each BillingGroup in BillingGroups for each BillingCode in BillingGroup.BillingCodes where1 = "..." next next
Problem 3: Here's the part where I don't have a clue. I need to dynamically create a query in LINQ to SQL. Keep in mind that I don't know how many groups there'll be or how many codes are in each group.
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.
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
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."
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) = _
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.
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"
How can I convert an object of type System.Data.Linq.DataQuery to System.Linq.IQueryable?I'm working with Visual Basic/Silverlight, and the source code of my query is as follows:
Public Function Get_Cli_Pag() As IQueryable(Of V_Cliente_Pagare) Dim Qry = From P In Me.Context.Pagares Join C In Me.Context.Codigos On C.Codigo
I know this topic is all over the place, but I am not doing an INSERT, UPDATE, DELETE. My statement is a plain and simple SELECT statement and so far has worked with 116 different items in my database until I got to one.
I have a search engine and am going through every single product in our database to add information to it. This is all done through the website, but when I search for ProductID 331 and click on it, it goes to the error page that says Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It doesn't make any sense to me that the website would error with only this one product. This is the statement I am using. Does anyone know why 1 product would be causing this error?
WebService: Public Class ProductSearch Inherits System.Web.Services.WebService <WebMethod()> _
I get the following errors: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. If the subquery only return one result,then it works fine,but if the subquery return more than one results, then the error occurred,how can I resolve this error?
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?
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]
I need to find all records where the status is in the local list of Integer's. In SQL I would do
Where Status in (select i from my list)
But I can't figure how to make it happen in Linq.
ListData = New IssueTrackerEntities2() Dim queryString As String = "SELECT VALUE CallList FROM IssueTrackerEntities2.CallList AS CallList" Dim Status As New List(Of Integer)
