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,
View 1 RepliesI've a collection with the data like this.[code]...
how can select the distinct data using linq?
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)
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.
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]....
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
View 3 RepliesI 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?
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?
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.
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've got a Dictionary(Of SomeEnum, Integer) that gets filled up while looping through some objects that have a property with type SomeEnum. Once the loop is done, I want the SomeEnum type that occurs the most in the list of objects. I also need the other counts as well for display purposes, hence the usage of a simple Dictionary(Of K, V).I am looking for a LINQ query to give me back the SomeEnum key that occurs the most by looking at each keys number of occurences. Or perhaps there's an easier way of going about it.I could do this:
Return (From kvp As KeyValuePair(Of SomeEnum, Integer) _
In Me.MyObjects Order By kvp.Value Descending _
Select kvp).First().Key
But wouldn't the sorting be a more expensive operation than trying to wiggle Max() in there somehow?
given this function:
Public Function Search(ByVal StartIndex As Integer, _
ByVal MaxResults As Integer, _
ByVal AccountNumber As String, _
ByVal LastName As String, _
[Code]....
instead of what I expected:
Select count(*) from remitline where lastname = "smith"
What am I doing wrong building up the where clause? I'm using Castle ActiveRecord 2.1
if there is that much of a performance gain in running a LINQ stored procedure versus a LINQ query?
View 1 RepliesI have to join two main tables, and I need to filter the results by elements in an ASP.NET web form. These filters are created on the fly so I have to use a lot of where extensions to filter the query. I want to execute the query with as optimized SQL as possible.
I am first doing a simple join between TW_Sites and TW_Investigators. Then there are two sub-tables that are involved. TW_InvestigatorToArea and TW_InvestigatorToDisease. While most of the where clauses are working fine, I have found a performance issue that won't be an issue right now, but will be an issue as the table gets bigger.
The arrays DiseaseCategories and DiseaseAreas would be the results of a CheckBoxList result.
Protected Sub LoadResults()
'Get Dictionary of Filters
Dim FilterDictionary As OrderedDictionary = Session.Item("InvestigatorFilterDictionary")
' Initialize LinqToSql
[code]....
Forgive my ignorance on this.I have this LINQ Query:Dim ngBikersDataContext As New CarBikeWalkDataContext
bikersList = (From c In ngBikersDataContext.Reg_Bikers _
Order By c.L_Name _
Select New Bikers() With { _
.BikerID = c.BikerID, _
.F_Name = c.F_Name, _
[Code]...
with the error "Overload resolution failed because no accesible 'Select' accepts this number of arguments."
Over the "NEW" I get an error " ')'expected."
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].....
We are doing a query against an in-memory collection of LINQ data objects. The wrinkle is that we are ordering by a column in a related table whose records have not necessarily been loaded yet (deferred loading:)
Dim oPkgProducts = _
From b In oBillPkg.BillProducts _
Where b.Successful.GetValueOrDefault(Common.X_INDETERMINATE) = _
[code]....
How do I add ROW_NUMBER to a LINQ query or Entity? How can I convert this solution to VB.NET?
[Code]...
I'm having trouble porting that last line. I have been unable to locate a VB.NET example. I'm actually not looking for any paging functionality like the example provides, just good old-fashioned Row_Number(Order By X) row index.
I was wandering is it possible to use a query within another query below is the code I am trying to use.
Public Function GetInventByComp(ByVal CompID As String) Using DC As New DataClassDataContext
'need to get company id's based off names? thats bad should be name from Id need to rethink this
Dim invent = (From C In DC.Inventors_Companies _
Where C.CompID = CompID _
Select C.InventorID).ToString
[Code]...
I was trying to us multiple values in a string like "1, 3, 5" but I can't seem to get that working either so I am trying to use just a single value now. Can anyone help me? "Yes I am new to this"
I'm having trouble getting a Linq to Sql query to work. Basically, I want the sum of a column in a table for only rows matching a certain condition. A simplified version is this:
Orders = order that contains products
OrderProducts = relational table containing products that are in an order
Products = table containing information about products
I can get the total sum of the qty of products like this:
Dim totalQty = requests.Sum(Function(p) p.OrderProducts.Sum(Function(q) CType(q.ProductQty, Nullable(Of Integer))))
(Requests is a IQueryable of Orders)But I also need to get the sum of qty where the actual product meets a certain condition. Something like...
Dim totalQty = requests.Sum(Function(p) p.OrderProducts.Sum(Function(q) CType(q.ProductQty, Nullable(Of Integer))))... WHERE p.OrderProducts.Product.ProductAttribute = true
How would I go about getting that query with the additional where clause to only get the sum of the qtys where the productAttribute = true ?
I have the following query which groups some records and then filters where the count of the grouped records is 1.
I'd like to take the returned result and perform another query to retrieve the entire record from the JobcodesWorkingRollup table where the ParentNode column equals the result of this query:
Dim query = From r In context.GetTable(Of JobcodesWorkingRollup)() _
Group r By r.ParentNode Into g = Group _
Where g.Count = 1 _
Select New With {.cnt = g.Count, .nm = g.FirstOrDefault.ParentNode}
I've been reading a fair bit about the performance of using LINQ rather than using a for each loop and from what I understand using a LINQ query would be a little bit slower but generally worth it for convenience and expressiveness. However I am a bit confused about how much slower it is if you were to use the results of the query in a for loop.
Let's say that I have a set called 'Locations' and a set of objects called 'Items'. Each 'item' can only belong to one 'location'. I want to link items that are under the same location to each other. If I were to do this using a normal 'For Each' loop it would be something like this:
For Each it as Item in Items
If it.Location.equals(Me.Location)
Me.LinkedItems.Add(it)
End If
Next
However if i was to use LINQ it would instead be this:
For Each it as Item in Items.Where(Function(i) i.Location.equals(Me.Location))
Me.LinkedItems.Add(it)
Next
is the second (LINQ) option going to loop once through the entire 'Items' set to complete the query, then loop through the results to add them to the list, resulting in essentially two loops, or will it do the one loop like the first (For Each) option?
Is it possible to do the following? [code] Basically I have one Load_Gridview function that is called on all postbacks, and rather than creating a bunch of different cases.I want the filters to stack.My actual code has more filters set up (4 or 5 of the).It all compiles ok but when I run and try to execute with active checked, or a department selected I get the following error. [code]
View 4 RepliesI have below XML file
<?
xml version="1.0" encoding="utf-8" ?><
PageFlow>
<
attributegroup name="Search"><
attributes>
[Code]...
I am looking to sum the "Status" field.
Dim _detailRecords As New DataTable
_detailRecords.Columns.Add("y")
_detailRecords.Columns.Add("z")
[Code]....
I'm also wanting to keep this as an IEnumerable of DataRow because I will be creating a datatable from it with
Dim yyyyy As DataTable = query8.CopyToDataTable
I am Fairly new to Linq and I am trying to write a simple query to return the error messages within my xml file.
<?xml version="1.0" encoding="utf-8"?>
<Error xmlns="urn:xxxxx">
The following errors were detected:
[code].....
if I run this under c#
from p in Addresses where p.Address2 == null select p.AddressID
it generate this query
SELECT [t0].[AddressID]
FROM [dbo].[Address] AS [t0]
WHERE [t0].[Address2] IS NULL
if I run this under vb.net
from p in Addresses where p.Address2 = nothing select p.AddressID
it generate this query
SELECT [t0].[AddressID]
FROM [dbo].[Address] AS [t0]
WHERE [t0].[Address2] = ''
p.Address2 is a varchar field that accept null value
why vb.net is "wrong" ?
I'm experimenting with linq compount selects. I find the following query not to return any element:
Dim q = From s In d.GetChildRows("DossierSinistri") _
From i In s.GetChildRows("DossierIncarichi") _
Select s
[Code].....
Why can't I get any values back when I use Linq to Sql? BHS_TimeSheet is my database table in which have some records. Model.TimeSheet is a class I create in the model. Private db As DataFactoryDataContext
[Code]...
Today I ran into an issue with LINQ to objects (not SQL) that popped up due to a typo. I had a .Select one place and a .Where in another place. I was expecting same result but they are showing different numbers. Assume somelist has 10 elements with all elements having qty = 0
[Code]...