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


ADVERTISEMENT

Cannot Iterate Of A Collection Of Anonymous Types Created From A LINQ Query?

Mar 17, 2010

Every LINQ example I have seen for VB.NET anonymous types claims I can do something like this:

[code]...

Now when I go to iterate through the collection(see example below), I get an error that says "Name "x" is not declared. For Each x in Infos It's like VB.NET doesn't understand that Infos is a collection of anonymous types created by LINQ and wants me to declare "x" as some type. (Wouldn't this defeat the purpose of an anonymous type?) I have added the references to System.Data.Linq and System.Data.DataSetExtensions to my project. Here is what I am importing with the class:

[code]...

View 4 Replies

Put Two Linq Query Results Together?

Sep 11, 2009

I have used linq quite abit but was just wondering how to append the results of two IEnumerable(of X) together into one IEnumerable(of X)

View 4 Replies

Asp.net - LINQ-to-SQL Query Returning No Results?

Mar 5, 2011

I have a query using LINQ-to-SQL. It queries an underlying database table Rooms. It uses Where conditions to narrow down the results, namely:

Gender.Current Occupancy < Max Occupancy Available Flag is Checked I know this should return results but it keeps returning an empty set.Code is below

[code]...

UPDATE: I've verified that the issue is with the statement where sh.is_available = 1, which doesn't make sense since this is a bit field.

View 3 Replies

LINQ To XML Query Returning No Results?

Jul 20, 2011

I'm doing some XLINQ in VB for work. I basically need to pull some values from a small chunk of XML as listed here:

<?xml version="1.0" encoding="utf-8"?>
<Fields>
<typeQtyRadioButtonList>1</typeQtyRadioButtonList>
<cmbQtyCheck>Reject</cmbQtyCheck>

[code]....

... Leaving out the implementation of the For Each loop....So I have stuck a break point on the for each and the collection has no elements in it.

View 1 Replies

Possible To Change &amp; To & In The Results From A LINQ Query?

Mar 3, 2009

I have the following code:

Dim db As New linqclassesDataContext
Dim categories = (From c In db.faq_cats)
NewFaqDropDownCategory.DataSource = categories

[code]....

View 3 Replies

Joining LINQ Query Results For Each Item Together?

Feb 3, 2011

In my application, I have a search query textbox (txtSearch) that works well with one-word queries with the following LINQ:[code]So how would I make the above query more generic (ie: able to handle any number of words in a query)?I should note that the object being queried is a List of strings pulled from an Access database. As it stands, this application only queries the database once and I'd like to keep it that way if possible.

View 3 Replies

.net - Detect Empty Results In A Linq-To-Entities Query?

May 29, 2009

The only way I know of is awkward:

'check for empty return
Dim count As Integer = (From s In myEntity.employee Where employeeID = myEmployeeIDVariable).Count
'If there is a record, then process

[code]....

View 2 Replies

Update One Entity Based On Results Of Another LINQ-to-Entities Query

Apr 9, 2011

I have the following LINQ-to-Entities query:[code]This will give me all the resident assignments for the current year/term. Then I have this LINQ-to-Entities query:[code]This will give me all the rooms. I want to iterate through the assignments and based on what room is assigned update the occupancy in reset_occupancy. I'm not 100% sure how to accomplish this. Here is my pseudo code of what I want to accomplish:[code]

View 1 Replies

Update One Entity Based On Results Of Another LINQ-to-Entities Query?

Dec 15, 2009

I have the following LINQ-to-Entities query:

' Get all the residency assignments that match the term/year.
Dim assignments = From p In dbContext.Residents _
Where p.semester = term _

[code].....

View 1 Replies

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

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

Get Results From A Query Into Multiple Textboxes?

Oct 17, 2009

I'm not sure how to do the following (in VB.NET), I'm hoping you will give me some insight or examples on how it can be done. My database is an SQL one.I have an assets table, which in it has the amount, growth percentage, income percentage. There are multiple assets per client.

I need to get the amount, growth and income into three separate boxes or variables and I'm not quite sure how to do this. The only other problem is that there would be two or three rows returned also, which need to have those three variables or textboxes again.
So for x amount of rows, I need 3 variables which the amount, growth and income will go into.

The reason I need everything into variables is that I can't find an effective way of showing each assets growth over x amount of years and the income it will produce into a data grid view. I'm able to do it if I specify what the growth and income are on the form, but not through the query.

View 1 Replies

Results From A Query Into Multiple Textboxes?

Aug 17, 2011

I have an assets table, which in it has the amount, growth percentage, income percentage. There are multiple assets per client.

I need to get the amount, growth and income into three separate boxes or variables and I'm not quite sure how to do this. The only other problem is that there would be two or three rows returned also, which need to have those three variables or textboxes again. So for x amount of rows, I need 3 variables which the amount, growth and income will go into.

The reason I need everything into variables is that I can't find an effective way of showing each assets growth over x amount of years and the income it will produce into a data grid view. I'm able to do it if I specify what the growth and income are on the form, but not through the query.

View 2 Replies

Assign The Results Of An SQL Query To Multiple Variables In .NET?

Mar 30, 2012

This is my first attempt at writing a program that accesses a database from scratch, rather than simply modifying my company's existing programs. It's also my first time using VB.Net 2010, as our other programs are written in VB6 and VB.NET 2003. We're using SQL Server 2000 but should be upgrading to 2008 soon, if that's relevant.I can successfully connect to the database and pull data via query and assign, for instance, the results to a combobox, such as here:

[Code]...

How do I execute a query so as to assign each field of the returned row to individual variables?

View 2 Replies

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

Group Joins With Multiple Tables Converting SQL To Linq

Mar 7, 2011

Can anyone help me by translating this SQL Statement to Linq? [code]

View 1 Replies

Query Multiple Tables In A VB2008 Program Using SQL?

Dec 13, 2009

I have a 4 table database and need to query at least 2 tables (the single table query works fine). The output goes in textboxes / labels on an existing form.

I'm trying something like this:

Private
Sub AddCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

[Code].....

View 12 Replies

DB/Reporting :: Optimize Query (aggregates On Multiple Tables)?

Feb 20, 2008

Code:

Tables:
- Company
- Department
- Employee
- CertificationA
- CertificationB

I am trying to return a recordset that gives some summary information about each company. The fields in my returned recordset should look like the following.

Company.Name, NumberOfDepartments, NumberOfEmployees, NumberOfCertificationA, NumberOfCertificationACompleted, NumberOfCertificationB, NumberOfCertificationBCompleted

There are actually about 10 different Certification tables but, with Certification A and B you should get the hang of it. When an employee starts a certifaction, a record is added to the that certification table.Below is a query I wrote that works (gets me the data I need), but I am unsure if it is the most efficient way. There will be quite a lot of data so performance is an issue.

Code:

Dim sqlNumberOfDepartments As String
sqlNumberOfDepartments = _
"(SELECT COUNT(*) " & _
"FROM DEPARTMENT " & _

[code].....

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

SQL Server 2005 - Select From Multiple DB's & Compile Results As Single Query?

Sep 25, 2009

Ok, the basic situation: Due to a few mixed up starts, a project ends up with not one, but three separate databases, each containing a portion of the overall project data. All three databases are the same, it's just that, say 10% of the project was run into the first, then a new DB was made due to a code update and 15% of the project was run into the new one, then another code change required another new database for the rest of the project. Again, the pertinent tables are all exactly the same across all three databases.

Now, assume I wanted to take all three of those databases - bearing in mind that they can't just be compiled into a single databases due to Primary Key issues and so on - and run a single query that would look through all three of them, select a given set of data from each, then compile those three sets into one single result and return it to the reporting page I'm working on.

View 5 Replies

Optimize Linq To SQL Query, Group By Multiple Fields

Jul 26, 2010

My LINQ query contains the following Group By statement:

Group p By Key = New With { _
.Latitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Latitude, _
.Longitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Longitude}

The query works, but here is the SQL that the clause above produces

SELECT [t6].[Latitude]
FROM (
SELECT TOP (1) [t5].[Latitude]

[Code]....

but this produced an error: "A group by expression can only contain non-constant scalars that are comparable by the server."

View 1 Replies

LINQ Query With Multiple OrderBy Statements Added In Loop

May 11, 2012

I have a method in a webservice that has parameter with which users can decide how they want to order their results. This is a List(Of String) with the names of the fields in the order they want to sort them. I know I can normally order on multiple columns by doing the following

Dim test = Bars.OrderBy(Function(x) x.Foo) _
.ThenBy(Function(x) x.Bar) _
.ThenBy(Function(x) x.Test)

However in this case this won't work since I can't chain the ThenBy function because I'm adding the sorting orders in a loop. To use ThenBy I need an IOrderedQueryable collection. This is how I would want it to work

Dim sortColumns = {"Foo", "Bar", "Test"}
Dim query = From b in Bars
For each column in sortColumns
Select Case column
Case "Foo"
query = query.Orderby(Function(x) x.Foo)
[Code] .....

View 1 Replies

Use Linq-to-sql To Iterate Db Records?

Apr 14, 2010

I asked on SO a few days ago what was the simplest quickest way to build a wrapper around a recently completed database. I took the advice and used sqlmetal to build linq classes around my database design.

Now I am having two problems. One, I don't know LINQ. And, two, I have been shocked to realize how hard it is to learn. I have a book on LINQ (Linq In Action by Manning) and it has helped some but at the end of the day it is going to take me a couple of weeks to get traction and I need to make some progress on my project today.

Click HERE To see my simple database schema. Click HERE to see the vb class that was generated for the schema.

My needs are simple. I have a console app. The main table is the SupplyModel table. Most of the other tables are child tables of the SupplyModel table.

I want to iterate through each of Supply Model records. I want to grab the data for a supply model and then DoStuff with the data. And I also need to iterate through the child records, for each supply model, for example the NumberedInventories and DoStuff with that as well.

For the record I have already written the following code...

Dim _dataContext As DataContext = New DataContext(ConnectionStrings("SupplyModelDB").ConnectionString)
Dim SMs As Table(Of Data.SupplyModels) = _dataContext.GetTable(Of Data.SupplyModels)()
Dim query = From sm In SMs Where sm.SupplyModelID = 1 Select sm

This code is working...I have a query object and I can use ObjectDumper to enumerate and dump the data...but I still can't figure it out...because ObjectDumper uses reflection and other language constructs I don't get. It DOES enumerate both the parent and child data just like I want (when level=2).

View 1 Replies

Linq Iterate A Control Collection?

Jun 29, 2009

Private Function GetWebDataGridOKButtonId() As String
Dim ctls As ControlCollection = _
WebDataGrid1.Controls(0).Controls(0).Controls

[Code]....

This is not working for me. I am trying to iterate a control collection and return one control ID.

View 2 Replies

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.

View 3 Replies

Select To Multiple Sql Tables Using Select Query?

Jul 23, 2011

I want to select to multiple sql tables using select query in vb.net..I have 2columns in sql table..I have tried this query.. But i'm getting error.

TextBox11.Text = Val(27)
cmd = New SqlCommand("SELECT outside,power FROM vijay21 INNER JOIN vijay22 ON vijay21.outside=vijay22.outside WHERE outside BETWEEN " & Val(TextBox11.Text) & " AND " &

[code].....

View 4 Replies

Results From Query - Populate Textbox Controls With Query Result?

Mar 11, 2010

how to take a query that returns a single row of data and load that data into textbox controls.I know about ExecuteScalar but it is only good for a single column of data from the query.

View 2 Replies

VS 2008 DataAdapter.Update To DataTables With Multiple Base Tables (Joined Tables)?

Jul 12, 2011

have a datagridview containing 2 tables left joined, so that:table1 LEFT JOIN table 2 ON table1.id=table2.idI get an error whenever I try to edit my datagridview."invalidOperationException was unhandled by the user codeDynamic SQL generation is not supported against multiple base tables."The error points to this line:

da.FillSchema(dt, SchemaType.Mapped)
da.Update(dt) << This line
'da = dataadapter

[code].....

View 3 Replies

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

View 1 Replies







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