I 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 RepliesI 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 RepliesI'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.
Given a table like
ID | Name | City
1 | X | Y
2 | Z | Y
[code].....
I 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]....
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
I'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]...
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'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
Given 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 RepliesI 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.
Are 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 RepliesHow 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]....
Im 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.
How 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.
I 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].....
i think i have not represented my question correctly here My LEFT OUTER JOIN MSDN reference is here to visualize my problem in access databaseTABLE "T1"1234567TABLE "T2"1,sunday2,monday i need output as 1,sunday2,monday34567 i need it to done through LINQso far i did is here
Dim RIGHT_table As DataTable = ds1.Tables("t1")
Dim LEFT_table As DataTable = ds2.Tables("T2")
Dim OutPut = From LEFTsource In LEFT_table.AsEnumerable(), RIGHTsource In RIGHT_table.AsEnumerable() _
[code]...
I'm trying to LINQ two tables based on a dynamic key. User can change key via a combo box. Key may be money, string, double, int, etc. Currently I'm getting the data just fine, but without filtering out the doubles. I can filter the double in VB, but it's slooooow. I'd like to do it in the LINQ query right out of the gate.
[Code]...
"Range variable name can be inferred only from a simple or qualified name with no arguments."
I'm trying to LINQ two tables based on a dynamic key. User can change key via a combo box. Key may be money, string, double, int, etc. Currently I'm getting the data just fine, but without filtering out the doubles. I can filter the double in VB, but it's slooooow. I'd like to do it in the LINQ query right out of the gate.
LinqMasterTable:
-------------------------------------------------------------
| AppleIndex | AppleCost | AppleColor | AppleDescription |
------------------------------------------------------------
[Code].....
I ultimately what I needed is generic function which would take two datatable and and 2 tablekeys and return Joined datatable. So here is my first step to solve it.
How Can I write Linq example of following T-SQL example in VB?
SELECT * FROM
Table1
LEFT OUTER JOIN
Table2
ON Table1.key = Table2.key
Let's say I have Plans and Documents
Dim myPlans = _context.Plans.Where(predicate1)
Dim myDocuments = _context.Documents.Where(predicate2)
I have structured the where clause for each using PredicateBuilder. So, myPlans and myDocuments have a correct SQL statement. What I'd like to do is join these two tables into one linq statement. The problem I'm having is that, by default the AND condition is joining the where clauses.
myPlans Where clause : (p.name like "%test%" AND p.name like "%bed%") OR (p.description like "%test%" AND p.description like "%bed%")
myDocuments Where clause : (d.name like "%test%" AND d.name like "%bed%") OR (d.description like "%test%" AND d.description like "%bed%")
When I combine the two the desired result Where clause is:
Where (d.ID = p.ID) AND
(myplans where clause above) OR (mydocument where clause above). Meaning, I'd like the two where clauses in each of the tables to be "or" instead of "And".
The current result where clause is:
Where (d.ID = p.ID) AND
(myplans where clause above) AND (mydocument where clause above). Meaning, I'd like the two where clauses in each of the tables to be "or" instead of "And".
I'm forming the statement like this:
Dim test = From d in myDocuments _
Join p in MyPlans on d.ID Equals p.ID _
Select d.Name, p.Name
I cannot get this to work with LINQ to SQL vb.net?
SELECT a.FLD_ID, a.FLD_SUBJECT, a.FLD_GROUP, a.FLD_SUBMITTED, a.FLD_LASTACTION, a.FLD_CLOSED
FROM TBL_WSS_TT_TICKET a LEFT OUTER JOIN
TBL_WSS_TT_ALLOW b ON a.FLD_ID = b.FLD_TICKET_REF
WHERE (a.FLD_USER = 'user') OR (b.FLD_USER = 'user')
I'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]....
I have two UNTYPED datatables: "Shelfs" and "Books". Data came from different databases.
I must create a "left join" relationship because I want a list of ALL shelfs and books (if they have any).
Dim dset As New DataSet
dset.Tables.Add(dtShelfs)
dset.Tables.Add(dtBooks)
[Code].....
I spent hours googling around, but all examples I found were only applicable to strongly typed dataSets, and how to build queries (not to get the data)
I could solve this the "easy" way: by looping both datatables and sending the rows to a third one, but I want to do this the "right" way and, of course... LINQ exists to make things easier and I don't know how to use it
I dont have a huge wealth of experince with SQL statements and im struggling;
[code]...
I would like to list all of the job titles(for which there could be multiples as different employees can have the same job title) in the employee table and find the maximum amount of hours for that job title as the hours could be different for different empoyees.
[code]...
I've long since built a way around this, but it still keeps bugging me... it doesnt help that my grasp of dynamic LINQ queries is still shakey.
For the example:
Parent has fields (ParentKey, ParentField)
Child has fields (ChildKey, ParentKey, ChildField)
Pet has fields (PetKey, ChildKey, PetField)
[Code].....
The above Join call doesnt work. I sort of understand why it doesnt work, but hopefully it'll show you how I tried to accomplish this task.
After all this was done I would have appended a Select to finish the job.
I tried it with the PredicateBuilder with little success. I might not know how to use it right but it felt like it wasnt gonna handle the joining.
I am using Subsonic3 and would like to get a Left Outer Join between two tables. Here is the tested SQL
SELECT a.*
FROM vwVendor a
LEFT OUTER JOIN dbo.pubvenmap b
on a.vend_no = b.vend_no
where b.vend_no is null
[Code]...
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?
I have used persistance class and used following LINQ query.[code]but it throws an error saying. "Specified Method is not Supported".How can i get data from two table using XPO Persistance Class?
View 1 RepliesI have been searching for quite a while about joining multiple datatables via LINQ to dataset (When i say multiple, i don't mean just 2!!!), and it seems there aren't lot of examples in the net. I have seen examples of joining two datatables, done that, no problem there. Now i want to join three datatables. And there's nothing i can find out there.For example i have a 3 datatables:
PERSON (columns: person_id, name, gender_code, ethnic_code)
GENDER (columns: gender_code, gender_description)
ETHNICITY (cols: ethnic_code, ethnic_description)
[code].....