ADO.NET :: Linq To SQL Group By Category And Count Each Subcategory?
Sep 30, 2010
I am wonder how I can generate a result table from linq to sql as below
Car sales table
Toyota small
Toyota passenger
Toyota small
Honda small
Honda small
Honda passenger
Honda small
result table
Brand small passenger total
Toyata 2 1 3
Honda 3 1 4
GridView1.DataSource = (from dtCarSales.AsEnumerable() .....
View 3 Replies
Similar Messages:
Mar 14, 2010
How do I translate the following query into LINQ to SQL?
[Code]....
View 1 Replies
Sep 2, 2010
i have a problem about listing. i have categories that it is added infinite category,subcategory. Table is like that:
id
ustid
kategori
seviye
i want listing like this:
For example when i click "Arac", the list will become like following:
Araç (category)
Otomobil (subcategory)
Motorsiklet (subcategory)
Then .when i click "Otomobil"
Araç
Otomobil
----Fiat
---Audi
---BMW
when i click "Fiat"
Araç
-Otomobil
--Fiat
---Murat
---Şahin
View 3 Replies
Jul 9, 2010
I have a category and a subcategory column in a Jqgrid. I have enabled inline editing, both category and subcategory are dropdownlists columns (edittype:'select'). I need to filter the subcategory list based on the selected category. I wonder how can I acheive this functionlity? I tried the below event but its not working for me
afterEditCell: function(rowid, celname, value, iRow, iCol) {
//to do here
}
the above event doesn't get fired. my all column are editable
View 1 Replies
Mar 3, 2011
I have a menu on my masterpage / defaultpage where I'm listing x categories. I would like to make a count of how many products there are in each category.
EX:
[Code]....
Obiously i need the specific categoryid, but how to I request that without having querystrings running since it is on the default page.
View 3 Replies
Mar 26, 2010
I'm using a linq group by query (with two grouping parameters) and would like to use the resulting data in a nested repeater.
var dateGroups = from row in data.AsEnumerable()
group row by new { StartDate = row["StartDate"], EndDate = row["EndDate"] };
"data" is a DataTable from an SqlDataAdapter-filled DataSet. "dateGroups" is used in the parent repeater, and I can access the group keys using Eval("key.StartDate") and Eval("key.EndDate").
Since dateGroups actually contains all the data rows grouped neatly by Start/End date, I'd like to access those rows to display the data in a child repeater. To what would I set the child repeater's DataSource? I have tried every expression in markup I could think of; I think the problem is that I'm trying to access an anonymous member (and I don't know how.) In case it doesn't turn out to be obvious, what would be the expression to access the elements in each iteration of the child repeater? Is there an expression that would let me set the DataSource in the markup, or will it have to be in the codebehind on some event in the parent repeater?
View 3 Replies
Jun 20, 2010
I want to create something like this using a DataList and Flow markup:
|-----------------------|
| Title |
|-----------------------|
| [x] Title |
| [x] Title |
| ... |
-------------------------
I have a table (modeled in Linq2Sql) Foo that has these fields
int id;
int? parentId;
string title;
Foo Parent;
EntitySet<Foo> Children;
Now, when there is a null parent, it means it's a top level category, and if the parent has a value, it's part of the category list.
I have created a DataList, and used a LinqDataSource with a query that looks like this:
[Code].....
This obviously doesn't work. How can I utilize the Children collection in a repeater of a DataList item?
View 1 Replies
Feb 8, 2011
I have a list of ids properly stored in a List<>. I now need to Group list and get a count of the number of occurrences sorted descending.
Example:
List<string> aryIDs = new List<string>;
aryIDs.Add("1234");
aryIDs.Add("4321");
aryIDs.Add("3214");
aryIDs.Add("1234");
aryIDs.Add("4321");
aryIDs.Add("1234");
Would Produce:
"1234", 3
"4321", 2
"3214", 1
This would be easy in TSQL, but I would like to avoid the server roundtrip, unnecessary tables, etc. if possible.
Update: The VB.NET conversion for Ralph Shillington's answer below:
[code]....
View 2 Replies
Jun 2, 2010
how to count total no of record in different group section in seagate crystal report
View 2 Replies
Nov 5, 2010
i create example using Northwind database so i create a new website and add new ADO.Net Entity Data Model (.edmx) called Northwind.edmx and i add Categories and Products table inside this (.edmx) file
and add new ADO.Net Data Servuce called "ADODataService" and add it as WebReferences called NorthwindService
so i add new web page and drag DropdownList and Gridview as i want to bind Dropdownlist to all categories and when i select category from Dropdownlist bind Gridview to all Products related to this category
so my code
[Code]....
and my code
[Code]....
so when i select category from Dropdownlist nothing happen :(
also you will find commented code in method BindCategory whuch is not work also.
View 1 Replies
Feb 7, 2010
I'll try to explain as simple as I can in text what my problem is. I don't see any other way to make my issue clear.
I have a GridView that uses an ObjectDataSource.
The ObjectDataSource has an DataObjectTypeName defined, so I pass/get complete objects or list of objects to/from the data access methods.
The objects I'm working with, let's say we work with Book objects, contain a Title and a Category, BUT the Category is an object itself. When the Select method is called, we get a List of Book objects, which is perfectly displayed, overridden ToString method in the Category objects within the Book objects.
The problem I face is that I can't find a way to update the category in my GridView. What I did already accomplish is:I used a template field in the GridView for the Category so I could use a DropDownList for that field in Edit mode.
I bound the DropDownList to another ObjectDataSource that gives me a list of CategoryObjects.
So when I enter Edit mode... the row shows a nice ddl with the available categories. It even selects the right original category.
So far so good... but when I want to save my new selection, I get an error that a string (the selected category in the ddl) cannot be converted to a Category object (that's what the Book object, that is going to be used for the update, expects).
View 5 Replies
Mar 30, 2011
I am working in a project that is a musical website. I want to display the web page with all categories I have. In my database , named Category_Master, I have two fields like Category_Id (which is a primary key ) and Category_Name. I have generated category_Id with NEWID() function. The category_Id is not supposed to display on the web page. But, i have to select Category_Name and according to that , I have to get the category_Id. If I am using Static connection with SqlDataSource, i am able to make visible=false property so that the category_id won't be visible. But , at that time, I can't write code to Select the category_id of the selected index.
If i am using disconnected approach, I filled the dataset with the Category_Master, and binded it to GridView. But, at that time, I can't hide the category_id from the user. I want to select category_id by clicking on the category_name with out displaying category_id ..?
View 4 Replies
Jan 25, 2011
I want to create a LINQ SQL-Group-Query... This is my LINQ-Query:
[Code]....
I want to Group this, like this normal SQL-Query do it?
[Code]....
Furtermore, when iam using this normal sql query the programm needs a Type .. for example Dim reqdata(of Iqueryable(of xxx)("sql query...") ... whats the Type of this ? When i select more tables than ones ? there is said thats <anonymous type> ?
View 1 Replies
Mar 28, 2011
I've a table with datetime null field in sql server. I'm trying to group the results based on the year in that field. But, when I wrote linq query, I didn't get intellisense for Year when I type that field. It only shows HasValue and Value. I think because it is converted nullable type. How do I do the grouping in this case?
View 5 Replies
Jun 25, 2010
I am trying to translate this:
SELECT IDNum, Max(Convert(varchar(20), (TimeOut - TimeIn), 108))
FROM IO_Times Group by IDNum
order by Max(Convert(varchar(20), (TimeOut - TimeIn), 108)) desc";
Into LINQ in C#. The above returns a list of the maximum value of (TimeOut-TimeIn) that corresponds to each unique IDNum (There are multiple TimeOut-TimeIn values for every IDNum).Here is what I am trying now:
from IO_Time in db.IO_Times
orderby SqlMethods.DateDiffMinute(IO_Time.TimeIn, IO_Time.TimeOut) descending
group IO_Time by IO_Time.IDNum into list
select new
{
ID = list.Key,
Time = list
});
This returns the correct IDs in the correct order, but the "list" contains everything in the database entry (which makes sense). I have also tried group by IDNum and Time, but then I get unique time entries and multiple IDs returned.I could do a foreach loop and manually sort it all out, but I would really rather not do that.
View 2 Replies
Dec 1, 2010
Im new for LINQ, how can i count the records in this code
[code]....
So i can say Count records and its = PValue so i can use that in a EVAL(PValue) and get the total value of the records.
View 1 Replies
Dec 9, 2010
I have T_articles to save the articles.
each article has authorCode , and categoryCode.
each article can be modified by the author several times.
and I save every modification in the T_articles in a difference row.
now I want to dislpay for each autor:
author name, article name , article category's name, last modified date
In SQL I should to it like that:
[Code]....
I try to write it in Linq so:
[Code]....
so I got error:
[Code]....
and again I got an error:
[Code]....
View 4 Replies
Aug 8, 2010
Using Linq to Sql how do i group the following 2 tables.
Orders Table:
CustomerID | Name |Date
1 | order1 | 2010-01-01
2 | order2 | 2010-01-01
2 | order3 | 2010-04-01
Calls Table:
CustomerID | Name |Date
1 | call1 | 2010-01-01
3 | call2 | 2010-06-01
2 | call3 | 2010-05-01
I want to group the two tables by date , Result:
Date | Orders | Calls
2010-01-01 | 2 | 1
2010-04-01 | 1 | 0
2010-05-01 | 0 | 1
2010-06-01 | 0 | 1
i know how to group a single table ,from o in Orders group o by o.Date.Date into og select new {Date = og.Key,Orders= og.Count()};
View 2 Replies
Nov 24, 2010
I need to find the count of records between the first and last day of the current month.I am very new to linq and having doubts in getting the count, below is the code i have written.
[code]....
View 1 Replies
Mar 24, 2011
I have a trivial issue that I can't resolve. Currently our app uses Linq to retrieve data and get a basic integer value of the row count. I can't form a query that gives back a count without a 'select i'. I don't need the select, just the count(*) response. How do I do this? Below is a sample:
return (from io in db._Owners
where io.Id == Id && io.userId == userId
join i in db._Instances on io.Id equals i.Id **select i**).Count()
;
View 3 Replies
Sep 9, 2010
I have this group-join syntax but I couldn't get the distinct value from the relational tables below.
Table_1
key_1 t1_value
1 Food
2 Sports
3 Leisure
4 Trip
5 Zoo
Table_2
key_2 key_1 t2_boolean
15 1 True
16 1 True
17 1 True
18 2 True
19 2 True
20 2 False
desire result:
[code]...
View 3 Replies
Jun 14, 2010
im trying to count the amount of users inside a chatroom, im trying to count a specific row using LINQ to SQL.
Heres the code
DataRowView dataRow = ((DataRowView)e.Item.DataItem);
string roomId = dataRow["RoomID"].ToString();
ChatRoomDataContext db = new ChatRoomDataContext();
var query = from c in db.ChatLoggedInUsers
where c.RoomID.ToString() == roomId.ToString()
select new { };
foreach (var c in query)
{
}
Where " select new { }; " is, is left blank as i am not too sure what goes there. I tried adding c.UserName.Count() but i got an error. what i need to put into that space to count the amount of records in the UserName column?
View 3 Replies
Feb 8, 2010
I'm attempting to further reduce a dataset/table by various user input. Part of this process is grouping the data by a timestamp prior to a summation. I'm trying to use LINQ and cannot get the data returned that I need.
[Code]....
View 7 Replies
Mar 17, 2011
I am facing an intresting problem now with entity framework. if i did a count of the some table with linq statement its give some count and it is not matching with my normal sql query count. my sql query returning count of all the records in that table where as linq query returns some count which is less than sql count. is this a problem with my edmx or table navigation properties..i do have some 5 to 6 child tables inside this table with foreignkey relation. will it do a join on each child table and returns only those records. I am very much confused and don't know how to slove this.
View 9 Replies
Mar 11, 2011
I have
Chart1["Series1"]
Chart1["Series2"] ....
It has multiple Series and each series has Several Data points.I want to find the count ( not sum but number of datapoints ) of Data points in all the series using LINQ.Currently i do
var count = from s in Chart1.Series
select new int[] { s.Points.Count };
And then for each thorough count and find the sum. Is there a better way to do this
View 1 Replies
