Asp.net - LINQ To SQL - Select SUM With A WHERE?

Aug 17, 2009

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 ?

ADVERTISEMENT

Linq To SQL Sub Select?

Oct 12, 2009

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}

Asp.net - Linq Select Compound From

Apr 16, 2009

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

Asp.net Mvc - Why Can't Get Anything Back When Use Linq To Sql Select

Feb 3, 2011

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

C# - Linq Ambiguity On Where And Select

Nov 15, 2011

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

LINQ Select In .NET And C# Differences?

Mar 16, 2011

When using VB.NET I can do the following:

Public Function GetUser(ByVal ID as Integer) As User
Dim dc As New YesEntities()
Return (From u in dc.Users Where u.ID = ID).Single()
End Function

And on my .aspx/.vbhtml page I can access the user's department name like this:

Dim DepartmentName as String = new User().GetUser(12).Department.Name

[Code]...

Select Controls Using LINQ?

Jul 25, 2010

i try to select controls using LINQ but it show error

here is the code:

Dim testcontrol As VariantType
testcontrol = From cControl As Control In Me.Controls.OfType(PictureBox) Select cControl Order By Name Ascending

[Code].....

[2008] Using Select With Linq

Jan 9, 2009

I have seen different examples in Linq and one used Select and another didnt but both return the same results. Is there a reason why we should use Select ?

[Code]...

.net - Linq To Object - Select Distinct

Mar 18, 2010

I can't quite figure out why this Linq Statement isn't working as i would expect:

[Code]....

I would assume that this would create a new collection of anonymous types, that would be distinct. Instead it creates a collection the size of the "ThisParentCollection" with duplicate "MyAnonymousType" in it (duplicate id's).

C# - Linq Select Certain Properties Into Another Object?

May 28, 2009

So say I have a collection of Bloops

Class Bloop
Public FirstName
Public LastName

[Code]....

Is it possible using Linq to select the FirstName and LastName out of all the Bloops in the collection of Bloops and return a collection of Razzies? Or am i limited to a For-Loop to do my work?

To clear up any confusion, either VB or C# will do. Also this will probably lead to me asking the question of (What about using a "Where" clause).

C# - Select` And `sub` Have Brackets Around Them In VB Linq Expressions?

Jul 12, 2011

I converted the following query from C#:

src.Select((c, i) => src.Substring(i)).Count(sub => sub.StartsWith(target))

To the VB.NET query:

src.[Select](Function(c, i) src.Substring(i)).Count(Function([sub]) [sub].StartsWith(target))

Using Developer Fusion. I was just wondering why the VB.NET version has [] throughout.

LINQ / Select Distinct From Dataset?

Aug 20, 2010

I have a single columned datatable inside a single tabled dataset.I just want to convert this dataset to distinct rows. Here is my code, it gives compile error '.' expected.

Dim query = _
From email In ds.Tables(0) _
Select email.Field<string>("Email").Distinct()

EDIT: I changed to (Of String) and it works... BUT NOW 'query' is an ienumerable collection of characters... not a datatable... so how do I convert back easily without manually doing a loop?

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)

Select Only Date Value No Time In LINQ To SQL?

May 17, 2009

I need to have a query which list all the user according to the date value in the database. Problems is this, in the database the date format is 5/5/2009 4:30:12 but I want to compare with 5/5/2009. I think the value of the based date is 5/5/2009 12:00:00 and that's why I couldn't query it.

The query is something like

dim db = new databcontext
dim user = from u in db.datacontext where u.signUpTime = 5/5/2009 select u.

Select Two Datacolumns From A Datarow In Linq ?

Aug 23, 2010

Dim orders = From tt In testTable _
Order By tt.Item("OrderNumber") _
Select tt.Item("OrderNumber"), tt.Item("OrderId")

This is breaking. Is there a way to do this? I would have thought it was easy enough. Obviously, I thought wrong....

Use If Statement During Select From Linq To Datasets?

Dec 14, 2009

I have this LINQ statement [code]...

Is there any way that I can use if/iif within select ?

Use Linq For Select Distinct Values?

Mar 28, 2012

I'm pretty new in Linq and the first problem I've is to select the Distinct values from a ObservableCollection( Of T)

I've tried with[code]...

.net - Reset Index In LINQ Select Clause?

Mar 13, 2012

My LINQ query:

Dim groupedData = (From p In pData _
Group By p.TruncParam Into Group) _
.SelectMany(Function(g) g.Group) _
.Select(Function(d, idx) New With { _

[Code]...

When the TruncParam changes, I would like the index (idx) in the Select clause to reset to 1. So, in the list above, the Index should be SOLUB0001, SOLUB0002, INSOL0001, INSOL0002...CLRES0001, SUMCA0001, SUME0001.

How should I alter the LINQ query?

C# - Get Access To An Incrementing Integer During LINQ Select

Dec 1, 2009

[Code]...

I am trying to replace tasks.IndexOf(t) + 1 with something a little simpler. Is there any built in functionality for this? Hrmm xml literals do not seem to translate well on here....

C# - LINQ - Select All In Parent-child Heirarchy

Mar 12, 2012

I was wondering if there is a neat way do to this, that DOESN'T use any kind of while loop or similar, preferably that would run against Linq to Entities as a single SQL round-trip, and also against Linq To Objects. I have an entity - Forum - that has a parent-child relationship going on. That is, a Forum may (or in the case of the top level, may not) have a ParentForum, and may have many ChildForums. A Forum then contains many Posts.

What I'm after here is a way to get all the Posts from a tree of Forums - i.e. the Forum in question, and all it's children, grandchildren etc. I don't know in advance how many sub-levels the Forum in question may have. (Note - I know this example isn't necessarily a valuble use case, but the Forum object model one is one that is familar to most people, and so serves as a generic and accessible premise rather than my actual domain model.)

Dynamic Variables In Linq Select Statement?

Nov 23, 2009

I have a listbox that is dynamically filled by certain values depending on what table has been selected from another list box. Once a value is selected, it is being graphed vs a date & time range. With my ignorance of linq: depending on what value is selected, the linq to sql statement I need to create to grab data from my database is different as I can't use an index on an anonymous type.

result = From t In db.APS _
Where t.DateTime >= startDate And _
t.DateTime <= finishDate And t.Weight = weight _
Select t.DateTime, t.TotalConcentration

t.TotalConcentration should be selected if my listbox value is "Total Concentration", but if it's something else, like "Temperature" or "Flow Rate" (connected to appropreate database columns) - this method obviously isn't going to work. I need to be able to dynamically select a specific column from the anonymous type list, or use some other method I'm unaware of to do this.

LINQ - Select A TYPED Item In A Collection?

Mar 1, 2011

Can I use LINQ to return an item in a collection that is the same TYPE as the items in the collection? I am a LINQ Noob trying to avoid looping.

Dim mostRecentlyCreatedQuestionnaire As ParentQuestionnaireItem =
CType((From P In questionnaireCollection Order By P.Metadata.CreateDate Descending).Take(1), ParentQuestionnaireItem)

I get an "unable to CAST" error when I include the CTYPE function. I kind of expected that error, but I imagine that if I coul dnot do this, LINQ's usefulness would be diminished and therefore assume that there must be a way..

Linq - Write Lambda Select Method In .net?

May 22, 2012

For I've tried this:Dim exampleItems As Dictionary(Of String, String) = New Dictionary(Of String, String)
Dim blah = exampleItems.Select (Function(x) New (x.Key, x.Value)).ToList 'error here

But I'm getting a syntax error and all the examples that I've seen are in C#.

LINQ To SQL Select Distinct From Multiple Colums?

Apr 2, 2010

I'm using LINQ to SQL to select some columns from one table. I want to get rid of the duplicate result also.

Dim customer = (From cus In db.Customers Select cus.CustomerId, cus.CustomerName).Distinct

Result:

1 David
2 James
1 David
3 Smith
2 James
5 Joe

Wanted result:

1 David
2 James
3 Smith
5 Joe

Can anyone show me how to get the wanted result?

Linq To SQL Transaction Insert Then Select Really Slow

May 25, 2011

I'm developing a piece of a system that basically migrates data from one set of tables to another set. Everything works fine, but I've decided to employ transactions instead of just failing on things that are partially completed. (That is, if some exception occurs, I want to rollback instead of having partial data migrated.)

I have a service (in the 3-tier architecture way, not web) which begins a transaction on the data access layer. The data context is shared in the data access class which contains many methods. Those methods use various LINQ-to-SQL techniques to update/insert/delete. All the LINQ-to-SQL "selects" are within CompiledQueries. [code]...

Select A Single Object Using Linq In Program?

Mar 18, 2011

I have done a lot of searching to what appears to be a simple LINQ problem but I can't figure out how to do grab an object out of a collection that has a specified minimum (or max value) without resorting to a sort like this[code]...

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 DataRow With Latest Date Using LINQ/

Jul 23, 2012

I have a datatable where there are 3 columns

ID | Name | Date
and i have 4 columns
1 | ABC | 20/07/2012

[code].....

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