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?

ADVERTISEMENT

Asp.net - Select Query In LINQ Based On Foreign Table

Mar 2, 2011

I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign key in Requests Table.

I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum.

Here is the way I implement that. I am looking to prevent using "For each".

iRefund = (From od1 In dc.OrderDetails _
Where od1.OrderID =1 _
Select od1.UnitCost).Sum

[Code]....

Select Query In LINQ Based On Foreign Table?

Mar 4, 2009

I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file.OrderDetailsID is a foreign key in Requests Table.I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId.And If there is a row in Requests Table for each rderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum

Select All Records Where A Field Is Null Using LINQ?

Jan 25, 2012

I've got a table that caches calculated values for certain dates. I want to use LINQ to select all rows where the calculated value fields are null.But when I use isNothing I get an error that LINQ can't translate this into T-SQL. Is there a way to select null values with link, like this...?

Dim var = From rec As Record In myDataContext.Records Where IsNothing(rec.calculatedValue) Select rec

Other posts on stackoverflow mostly discuss how to avoid or check for null values with LINQ.

Note: I can't just set cacluatedValue's default to -1 to flag records whose calculated value has not been set (and then select those records) because I run queries that sum/average the calculated values. Selecting nulls seems cleaner and less bug-prone.

Select Null Records Produces Exception Error

May 29, 2012

Not sure what I am doing wrong, but here is the code.

SQL Server Express 2008 R2
VB 2010
Private Sub FillDataSetAndViewGrid()

[Code].....

Linq Query Handling Null Values?

Nov 12, 2011

From r In ReceiptLines
Where
r.RECEIPT.RECEIPTDATE >= _reportStartDate
And r.RECEIPT.RECEIPTDATE <= _reportEndDate

[Code].....

I am fetching all departments and their sales, average, count from the ReceiptLine, Receipt, ReceiptDiscount tables. The problem i am facing is, if i remove where discount > 0, I am getting null exception. But if I include that, then I only get sales that has discount. How would I write query that bring all sales less discount (if it has one).

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.

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

Linq To Datarow, Select Multiple Columns As Distinct?

Apr 16, 2010

basically i'm trying to reproduce the following mssql query as LINQ

SELECT DISTINCT [TABLENAME], [COLUMNNAME] FROM [DATATABLE]

the closest i've got is

Dim query = (From row As DataRow In ds.Tables("DATATABLE").Rows _
Select row("COLUMNNAME") ,row("TABLENAME").Distinct

when i do the above i get the error

Range variable name can be inferred only from a simple or qualified name with no arguments.

i was sort of expecting it to return a collection that i could then iterate through and perform actions for each entry. maybe a datarow collection?

As a complete LINQ newb, i'm not sure what i'm missing.
i've tried variations on

Select new with { row("COLUMNNAME") ,row("TABLENAME")}

and get:

Anonymous type member name can be inferred only from a simple or qualified name with no arguments.

to get around this i've tried

Dim query = From r In ds.Tables("DATATABLE").AsEnumerable _
Select New String(1) {r("TABLENAME"), r("COLUMNNAME")} Distinct

however it doesn't seem to be doing the distinct thing properly.

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

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

VS 2008 - Create A Linq Filter To Select Only Unique Records For TableName + PKs

May 22, 2011

Basically i have a list(of TransRecord)

vb Public Class TransRecord
Public TableName As String
Public PKs As String

[CODE]...

And want to create a linq filter to select only unique records for TableName + PKs (group by) ... but want it to select only the record with the highest ID for this group by.

Asp.net - Select Distinct Rows From A Datatable With Criteria Involving Multiple Columns Using LINQ

Mar 9, 2012

I have a datatable as shown in the figure. Let me explain my required based on this image. I have 7 rows of data. The rows 1 and 2 contains columns till UnitSqcNo same. I want only row 3 among the two. In general I want select all the rows with distinct model, unittype, unit and rest with greater CompId. ie the table should look like

Linq To SQL Query To Select Maximum Value

Jun 21, 2010

Linq to SQL query to Select the maximum "MeanWindSpeed" value? The database name is "WeatherArchives" The TableName is "TblValues" The Column is "MeanWindSpeed" And Also, I would like to have a query to get me the Max "MeanWindSpeed" in each year,

Select Distinct In Linq Query?

Jan 4, 2012

I've a collection with the data like this.[code]...

how can select the distinct data using linq?

Select More Than One Column In A Linq Query?

Nov 2, 2009

Dim MyQuery = From c In xdoc.Descendants() _
Where c.Attribute(OriginY) IsNot Nothing _
Order By Val(c.Attribute(OriginY).Value), Val(c.Attribute(OriginX).Value) _
Select c.Attribute(UniStr)

Right above you can see my First! linq attempt! And here comes my first question.

How can i select more than one column in a linq query in vb.net?

For example... Select c.Attribute(UniStr) AND c.Attribute(OriginY)

.net - Write A Linq To Sql Query To Find Records Where Field Name Can Match One Of Dynamic Number Of Strings?

Mar 21, 2012

I have users check off lab facilities in a UI. I want to use linq to fetch corresponding records for all of the labs that they have checked off. Basically,

Dim myRecs = (From l As EpiData In myDataContext.EPIDatas Where l.facility= _
one of the checked labs

So basically, I need to write a linq query where the "strings" to match are determined at runtime. Is there any way to do this easily? I know that there is a library out there called dynamic LINQ, but (1) it's in C# and I'm writing in VB (2) I'm really just looking for a single, simple solution for this single case.

Set Two Foreign Keys, That Refer To The Same Table/field

Feb 22, 2011

I have an Access database where I want to store the contacts in a company: In this database I have a table named "users" where I keep the users which will have access to the database. In the main table, named "contacts", two of my fields "insert_user" and "contact_user" refer to the user who entered the contact information and the user who actually made the contact. The way I am thinking it, I will have to set both of the fields as foreign keys to the same table/field, namely "users"/"username".

So:
I set two foreign key constrains in two different fields ("insert_user", "contact_user"), which refer to the same table/field ("users"/"username") in an
Access database, using VB.Net OleDB (See the SQL Statement below)

ALTER TABLE contacts ADD CONSTRAINT insert_user_foreignkey FOREIGN KEY (insert_user) REFERENCES users (username)
ALTER TABLE contacts ADD CONSTRAINT contact_user_foreignkey FOREIGN KEY (contact_user) REFERENCES users (username)

I open the database with MS-Access. I see that all the tables are fine (as they should be).

However when I click on relationships at Access I get all my tables plus a table named users_1 connected to "contacts"/"contact_user" (the table users is only connected to "contacts"/"insert_user")

It is very strange that in the Table View of Access I don't see this table, but only in the Relationships View.

Questions

Is my thinking right (to set 2 foreign keys, that refer to the same table/field)? What is this table "users_1"? Will my database work or am I doing something wrong?

Return The Number Of Records Processed From An Sql Procedure Whether It Is A Select Or Update/insert Query?

Nov 25, 2009

how can i return the number of records processed from an sql procedure whether it is a select or update/insert query and how can i receive it in the calling method in vb .net.

Visual Studio 2010, Access DB - How To Retrieve The Number Of Records For A Select Query

Aug 13, 2010

I am new to VB and trying my hand at the below code. I am trying to determine the number of records for each possible combination. Depending on the outcome of the number of records groups may be combined. I am not able to find the record count for each grouping. Can you please look at the code below and suggest what I am doing wrong?

[Code]...

.net - In Linq To Entities - How To Do An Inner EXISTS (SELECT 1 FROM - Type Of Query

Mar 6, 2011

I am trying to write a LINQ equivalent of

SELECT C1, C2, C3
FROM T1
WHERE T1.C4='xyz' AND
EXISTS (SELECT 1 FROM T2
WHERE T1.C17 = T2.C24)
ORDER BY C3

I'm using EF CTP 5, so I have a DBContext variable named dbc, which includes DBSet objects T1s and T2s, based on POCOs T1 and T2.

In LINQ I write

DIM IND = From i In dbc.T1s
Where i.C4 = "xyz"
And (From t In dbc.T2s Where i.C17 = t.C24).Any
Select i.C1, i.C2, i.C3
Order By C3

Running the query I get the error message "Unable to create a constant value of type 'T2'. Only primitive types ('such as Int32, String, and Guid') are supported in this context." When I omit the inner expression (third line in the LINQ code), the query runs fine. I tried switching the orders of the inner comparison, to be t.C24 = i.C17, with no effect.

Displaying Text Fields Linked To By Foreign Keys?

May 8, 2009

I have a three-tier Windows Forms DB application in VB.NET. I'm using VS 2005. I'd like to display records in a table in a DataGridView. I can already display the records as is by binding the DataSource to the business class that talks to the DB class:
Dim assetList as List(Of Asset)
assetList = DB_Asset.GetAssetListOrderByID_Asset
AssetDataGridView.DataSource = assetList

"Asset" is my business class, and "DB_Asset" is my DB class that queries the DB to return assetList. Now, Asset has members something like this:
Private m_ID_Asset As Integer
Private m_CategoryID As Integer
Private m_CustodianID As Integer
Private m_ManufacturerID As Integer
Private m_SignedOutToID As Integer
Private m_DefaultLocationID As Integer
Private m_CurrentLocationID As Integer
Private m_DateAcquired As Date
Private m_DateEntered As Date
Private m_EnteredByID As Integer

m _ ID _ Asset contains the primary key for the Asset table in the DB, and everything else of the form m_XXXXXXXXXID contains a foreign key to another table in the DB. So basically what I get now is rows with a whole lot of numbers. It's exactly what's in the Assets table:

ID_Asset CategoryID CustodianID ManufacturerID SignedOutToID
1 17 23 14 5

What I'd like to know is if there's an easy way to display the text fields that I've linked to with all of those foreign keys:

ID_Asset CategoryName CustodianName Manufacturer SignedOutTo
1 Soda - Diet John Coca-Cola Fred

Pass A Parameter From The Select List Into A Function For Joining A Linq Query?

May 25, 2010

I have a query that can be summarised in SQL as follows;

Select
S.StockCode
From
StockToCheck As S

[Code]....

Where the S var is a list of strings. This gives the error 'S' is not declared and points to the S in the function call / join (GetPOSStock). So it does not seem possible to do this in Linq, can anyone confirm?

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

Exception When Building LINQ Query With Multiple Levels Of Delegates Or Lambdas ("Value Cannot Be Null. Parameter Name: Instance")?

Jun 22, 2011

've got a function that takes two parameters (a delegate and an integer) and then creates a LINQ MethodCallExpression, which it uses to gets the results:

Public Delegate Function CompareTwoIntegerFunction(ByVal i1 As Integer, ByVal i2 As Integer) As Boolean
Public Function Test(ByVal pFunc As CompareTwoIntegerFunction, ByVal i1 As Integer, ByVal

[code].....

Update An Object's Foreign Key Value With LINQ To Entities

May 4, 2012

[code]How do I update the ProdServiceID in the database using LINQ to entities?

LINQ To SQL Query - Select Customer From Dropdownlist And Then Gridview - Load All Items Of All Orders Of That Customer

Oct 4, 2011

I have the following problem: I select a customer from a dropdownlist and then the gridview should load all items of all orders of that customer. I have the following query:

Dim allorders = From ord In db.Orders

Where ord.CustomerID = Convert.ToInt32(CustomerDropDownList.SelectedValue)

[CODE]..............

I also tried to modify the query as follows:

Dim orderitems = From oi In db.OrderItems

Where oi.OrderNumber = (From From ord In db.Orders

[CODE]...........

But this does not work. I just started using LINQ.

LINQ Query Is Enumerated - Turn On Some Kind Of Flag That Alerts Me Each Time A LINQ Query Is Enumerated?

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.

Linq To Sql Null - Check Whether A Column Is Null

Jul 15, 2011

How to check whether a column is null i use the following code but I got this error "Input string was not in a correct format."

[Code]....

Visual Studio 2010 - Failed To Enable Constraints - One Or More Rows Contain Values Violating Non-null - Unique - Or Foreign-key Constraints. Error

Dec 9, 2011

There were three similar questions in StackOverFlow but none gave an answer.. If have found why this error in occurring but don't know the fix. I am using Strongly Typed Dataset for my project which is created as a dll for DAL. I have added the Sql Server Table into this dataset using the designer and has created a DataAdapter

[Code]...

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