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
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 RepliesGiven 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'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 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 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].....
I'm basically trying to emulate a query like the below in LINQ:
DECLARE @id INT
SET @id = 1
SELECT
q.txtLQRdisplayName AS DisplayName,
[code]....
I've got what I think is a working left outer join linq query but I'm having problems with the select because of null values in the right hand side of the join.[code]I want to return all of e and one column from c called tClassCode. I was wondering what the syntax would be. As you can see I'm using vb.net.[code]If i remove the c.tClassCode from the select the query runs without error. So i though perhaps i needed to do a select new but i don't think i was doing that correctly either.
View 2 Repliesi am having 2 tables one table stores the WEEK-days another table stores the WEEK-days + other fields.i need the LINQ query to return all WEEK-days + other fields just like SQL LEFT JOIN query so far i did is [code]....
View 5 RepliesThe query works but only retrieve the joined rows, i want to retrieve all rows in the first datatable.My current query:
vb.net
Dim q = From regE In (From registoE In _dtRegistosAEnviar.AsEnumerable
Group registoE By colaborador = registoE.Field(Of String)("Colaborador") Into grupoE = Group
Select New With {
[code]....
I'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.
I have three tables Student, TimeSheet and TimeRecord.
Talbe columns:
Student : StudentId, FirstName,
LastName
TimeSheet: TimeSheetId,StudentId, IsActive
[Code].....
Given a table like
ID | Name | City
1 | X | Y
2 | Z | Y
[code].....