LINQ Query - Join Tables On Nullable Columns

Apr 17, 2011

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

View 2 Replies


ADVERTISEMENT

C# - LINQ To SQL Join 3 Tables And Select Multiple Columns And Also Using Sum

Aug 19, 2010

I have three tables Student, TimeSheet and TimeRecord.

Talbe columns:
Student : StudentId, FirstName,
LastName
TimeSheet: TimeSheetId,StudentId, IsActive

[Code].....

View 4 Replies

C# - LINQ To SQL Join Two Tables To Select Parent Table Twice Based On Two Different Columns From Child Table?

Aug 23, 2010

I have two tables Employees and CafeLogs. Some employees can be cashiers and also customers at the Cafe shop.

Table structures:

Employees: EmployeeId(PK) , FirstName, LastName
CafeLogs: LogId (PK), CashierId, EmployeeId, Value, => CashierId and EmployeeId are the data from column EmployeeId of Empoyee table

Table relationship:

Employees 1:N CafeLogs (CashierId (FK))

[code]....

Right now I know how to select only LogId, Employee's name, and , Value, not with Cashier name yet.

Dim query = From log In db.CafeLogs _
Join emp In db.Employees On emp.EmployeeId Equals log.EmployeeId _
Select log.LogId, emp.FirsName, emp.LastName, log.Value

View 1 Replies

LINQ To SQL And Join Two Tables With OR Clause

Apr 23, 2009

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

View 2 Replies

Can't Use A Where Clause On Both Tables In A LINQ Group Join?

Jun 7, 2011

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?

View 1 Replies

Equivalent To JOIN Query With Dataset Multiple Tables?

Dec 21, 2011

How to Query multiple tables in a dataset to form single consolidated table without using any external database. I know this can be done using 'JOIN' in SQl.

Now my condition is I have multiple tables filled with data in a dataset and each table is interrelated with a common 'column'. For example ,

Table 1
ID
Name
1

[Code].....

Here I mentioned only two tables but in my case there are many tables. I need a generic answer for this dynamic query .

View 8 Replies

Join - Added Three Tables To A LINQ To SQL Class - Then Connected To The Data Sources

Jan 23, 2012

I have three tables: BOOKS(BookID, Title, Author) CLASSES(ClassID, Title, MajorID,BookID) and MAJORS(MajorID, Description). I added these three tables to a LINQ to SQL class (UBooks.dbml) and then connected to the data sources. Then declared DataContext.

Using LINQ, my goal is to display all books in a grid which I have done with:

Dim allBooks = From Books In db.Books _
Order By Books.BookID _
Select Books

Me.BookBindingSource.DataSource = allBooks

Next I need to display all majors in a listbox. I would like the description to be shown however I am only getting the majorID to display with the code

lstMajors.DataSource = db.Majors

I have tried db.Majors.Description, however I am told then that Description is not a member of the LINQ table. and my final goal is to display all the books for whichever Major is selected in another data grid. This is what I am primarily concerned with right now. When using the SQLClient classes, I was able to create a string joining the tables (also had the description properly showing in the listbox, but that is of little importance right now to me). In the video tutorials I was able to watch whenever the tables were dragged from the database into the OR designer of the LINQtoSQL.dbml class file all of the associations were already there and the narrator only really had to do some drag and drop. Not only is drag and drop not working without these associations, but I'd much rather learn the code behind it.

View 4 Replies

Asp.net LINQ Join Query

Sep 28, 2011

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 Replies

Join LINQ Query Result And Arraylist In VB

Dec 5, 2009

I 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 Replies

Asp.net - SQL Query Conversion To LINQ Left Outer Join?

Jul 4, 2009

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.

View 1 Replies

C# :: Aggregate Or Join Strings In Linq To Sql Query (SQL Server)?

Aug 6, 2010

Given a table like

ID | Name | City
1 | X | Y
2 | Z | Y

[code].....

View 2 Replies

LINQ Join Query On Datatable And Arraylist Error?

Dec 6, 2009

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]....

View 1 Replies

Multi - Table Join Query In ASP.NET To SQL Server Via LINQ?

Jan 29, 2012

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]...

View 3 Replies

SQL Query With Three Tables And Sum Of The Product Of 2 Columns Of One Table

Sep 10, 2011

I am having 3 tables in my DB Challans, Challan_Details & Customers. They are as follows

Table Challans:

Customer_ID (Related with Customer_ID in Customers Table) Challan_ID (Key) Challan_VchNo (Voucher No.) Challan_Date Invoice_ID
Table Challan_Details

[Code]....

View 3 Replies

LINQ: "Group Join" Functionality Joining Three Tables

Jul 21, 2010

I'm working on becoming familiar with LINQ, and getting my head around the syntax and usage.

[Code]...

View 1 Replies

Inner Join - Join Two Tables,'Employee' And 'Dispatch'

May 17, 2012

Iwant to join two tables,'Employee' and 'Dispatch'. Dispatch has column 'DispatcherID' and 'TechnicianID' which are both foregn keys to EmployeeID in Employee table.I want to join these two tables using EmployeeID so that i can obtain the matching name to each id.but it only works when i make a single join to either DispatcherID or TechnicianID and not for both.

[code]

select Employee.firstname+' '+Employee,secondname as Technician,Employee.firstname+' '+Employee.secondname as Dispatcher from Dispatch inner join Employee on Dispatch.TechnicianID=Employee.EmployeeID inner join Employee on

[CODE]...

View 12 Replies

How To Group Distinct Columns In A Linq Query

Nov 4, 2010

The following data is created by joining two sql tables together:I would like to group together distinct rows of DateStamp/UserName/ StudentName/ InstructorName/TableName/PrimaryKey (I'll call this 'group records') and then group under these ColumnName/PreviousValue/NewValue (I'll call this 'subgroup records')The end result would be that I could iterate through the 'group records' - there would be 5. In each 'group record', I could then iterate through the 'subgroup records'. The 5 groups would contain 3, 2, 5, 2 and 1 subgroup records respectively.What would be the syntax to create a query to do this? Ideally this would be in a vb.net linq syntax.

View 1 Replies

Iterate Through LINQ Query - Results From Multiple Tables

Dec 7, 2010

I 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?

View 1 Replies

.net - Proper Syntax For LINQ Query Selecting Only Certain Columns?

Sep 13, 2011

What is the proper syntax for this:

Dim qry As <??> = From f In dirInfo.GetFiles("*.QBW") Select File = f.FullName, Include = True
Dim dt As DataTable = qry.CopyToDataTable()

I tried as "IEnumerable(Of DataRow)" but that didn't work. At runtime it said:

Unable to cast object of type
'WhereSelectArrayIterator2[System.IO.FileInfo,VB$AnonymousType_02[System.String,System.Boolean]]'
to type
'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

View 5 Replies

Sql - Group By Having And Count As LINQ Query With Multiply Nested Tables?

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

Update Tables Using Bindingnavigator Save Button From Join Tables

Oct 29, 2010

In my datagridview I was fill in using join tables (table1 = pr_employees, table2 = pr_employee_deduction). My problem now is during save button I want to update records from table2 only.[code]

View 3 Replies

C# - Joint Query In Linq To SQL To Select All Records Even There Are Some Null In The Columns Of Foreign Keys?

Feb 2, 2011

I'd like to take all suggestion either in C# or VB.NET.I have a DB diagram like the image below. I also include the database script here [URL]In the Students table, CountryId and RoomId column are allowed null. Because some records do not have info about room and country yet.Also, some students do not have essays.I'm doing a joint query with all tables. I want to select all students to project the result like this:Wanted query result.

Here's my current query that gives the result like the image below:

Dim db As New DBDataContext
Dim query = From st In db.Students _
Join c In db.Countries On c.Id Equals st.Id _
Join r In db.Rooms On r.Id Equals st.RoomId _

[code].....

current query result I got only one result back because I have (2) William NoMan record in every table. But I don't get anything about others, like (3) Sync Master who has everything but RoomId.What do I need to modify the query above so it will give me all students like in the wanted query image above?

View 2 Replies

How To Join Tables

Sep 1, 2010

I've been using Java for a number of years, and now I'm at the beginning again with VB.NET.How would I go about joining two tables?- order doesn't matter, as long as i can print/display the order
Here's an example of what I'd like to do:

table 1
data 1 , data 2 , date time
12 , 23 , 2010/8/20 10:00:00

[code].....

View 4 Replies

SQL Join On Two Tables?

May 19, 2010

I'm trying to do a join on two tables but i'm having a hard time. Here's the table schemes:

table1 : tbl_Trainees_IntentToParticipate
Scheme:
TraineeID (PK)
FName
LName

I want to select all TraineeID/FName/LNames from table1 where TakenTest (from table2)= false

heres the sql statement I have so far:

SqlJoinStatement = "SELECT tbl_Trainees_IntentToParticipate.FName, tbl_Trainees_IntentToParticipate.LName" _
& "FROM(tbl_Trainees_IntentToParticipate, tbl_Trainees_TabScores) " _

[code]....

View 2 Replies

.net - Ms-Access: Join 3 Tables?

May 23, 2009

how to join 3 tables, I have the following statement but I'm getting a missing a (syntax error "operator error")

da = New OleDbDataAdapter("SELECT [S].[Scheduled Vege], [V].[Description],
[DS].[Task], [DS].[Task Date], [DS].[Completed] FROM [Scheduled] AS S
INNER JOIN [Date Schedules] AS DS ON [S].[SchedID] = [DS].[SchedID]

[code]....

View 3 Replies

DB/Reporting :: Join Two Tables IN SQL?

Jun 16, 2010

I am working on a Visual Basic 2008 program using an Access 2000 database. My SQL codereturns no records, but I know the table has a record. A simplified example of the code looks like this:

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table2. Field1, Table2.Field2 FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table1.Field2 = Table2.Field2 WHERE Table1.Field1=@Field1 AND Table1.Field2=@Field2;

[code].....

View 2 Replies

VS 2010 Join Tables From 2 Different DB's?

Nov 30, 2011

Is it possible to join a table from a SQL Server DB and an oracle DB?

View 1 Replies

.net - Join 2 Tables With A New Compute Field?

Mar 14, 2012

here what i try to get:

Table A:
Field: Name A
Field: Id_A

[Code]....

In fact i want to list in grid the table A with a column which contains a checkbox and is checked if the item is include in table B (field TableA_Id_A).

View 2 Replies

Join 2 Tables From 2 Different Access Databases?

May 22, 2012

I am tying to build a COMMAND that joins two tables from two different access databases in vb.net,

View 4 Replies

Populate INNER JOIN Tables Into DATAGRID?

Jan 27, 2010

I used INNER JOIN to bind the two tables and my problem is how to populate/show on the datagrid.[code]....

View 12 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved