C# - Linq - Group By Multiple Tables?

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


Similar Messages:

ADO.NET :: Can't Group With Linq When Join Several Tables

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

ADO.NET :: Linq On Multiple Tables With Same Columns

Aug 17, 2010

I have a partitioned db where several tables have the exact same structure. I'm able to query the tables using linq, but only by quering the individual tables 1 at a time and by using the explicit table name. For example at the moment, I have 8 tables so I have something similar to the following:

[Code]....

Is there any way, that based on a certain value (stringValue), I could determine which table to query and then code the linq query just once?

View 2 Replies

ADO.NET :: Searching Through Multiple Tables (Linq To SQL)?

Aug 12, 2010

I have a couple tables that are kind of unrelated - id like to search through both of them and create a type that i can sift through later

something like this doesnt work

[Code]....

I basically want to create a list of "AnimalSearchResults" that contains all dogs and all cats that have that name Whats the best way to do something like this?

View 6 Replies

ADO.NET :: Linq Examples With Multiple Tables - Using PK And FK In A Dataset?

Aug 19, 2010

Does anyone have some really great tutorials on how to use link with datasets that contain multiple tables that utilise primary key and foreign key relations? For eample

Table1
- id (PK)
- language
Table2
-id (PK)
-programmingword
Table3
-id (PK)
-languages_ID (FK)
-pgrammingword_ID (FK)
-translatedword

Linq search for: Table3.translatedword where Table1.Language ='french' and Table2.programmingword = "yes" does this make sense? im looking for exampes/ tutorials for how to do this? or can anyone recommend any good books on linq?

View 3 Replies

ADO.NET :: LINQ To Entity Error Seeing Relationships Across Multiple Tables?

Jan 24, 2011

I'm a newbie to this so bear with me. From what I've read online LINQ to Entity should see the relationships without having to specify the joins between multiple tables. I have a relationship that goes across 4 tables Employee, EmployeeDepartmentLink, EmployeeProjectLink, and Project. The idea is an employee can be in multiple departments and work on multiple projects with it being specified which department the employee is in for that project.

This is a C# MVC3 program connecting to a SQL database using VS 2010 Express.

I have no problem with running this query to find employees on a project:

[Code]....

Tring to do the reverse, finding projects that an employee worked on results in an error:

[Code]....

Error message: The specified type member 'EmployeeDepartmentLinks' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. So I have 2 questions,

1) Why would it work in one direction and not the other? I've played around with it for a couple days with the same results.

2) Does it matter that my primary key and foreign keys are not named the same since all the relationships are connected in SQL? Ex: in Employee (EmployeePK)--(EmployeeFK) in EmployeeDepartmentLink Trying different stuff in LINQPad I was able to get the 2nd query to work if I specified all the joins which I thought was not neccesary with LINQ to Entity. I converted an SQL query that I made on the server into a LINQ query below which works.

View 5 Replies

DataSource Controls :: How To Select Multiple Tables By Using LinQ In MVC

May 19, 2010

I'm using ASP.NET MVC to create a small web apllication.

Here is my database And here is my class

[Code]....

View 2 Replies

ADO.NET :: LINQ To SQL - Reuse Table Classes For Multiple Tables?

Jan 21, 2011

Is it possible to create a table class that can be used for multiple tables? For example, if I have some class:

[Code]....

In LINQ to SQL, this maps to a table with a name based on the class name, DBClass. However, suppose I want to use that class for multiple tables, and it could be an arbitrary number of tables. Is this possible

View 3 Replies

Databinding Multiple Tables Linq Query To Gridview?

Jun 3, 2010

how can I display a linq query in a gridview that has data from multiple tables AND allow the user to edit some of the fields or delete the data from a single table?

I'd like to do this with either a linqdatasource or a linq query. I'm aware I can set the e.Result to the query on the selecting event. I've also been able to build a custom databound control for displaying the linq relations (parent.child).

However, I'm not sure how I can make this work with delete? I'm thinking I may need to handle the delete event with custom code.

View 2 Replies

C# - Foreign Key Constraint Issue When Deleting From Multiple Tables Using Linq To Sql?

Feb 27, 2011

I have two tables with a foreign key constraint how can I delete rows from both of them in one transaction? linq to SQL seems to call my deletes in the wrong order. Is there somewhere I can check and make sure the constraint is recognized properly by linq to SQL??

DataContext.OtherImages.DeleteOnSubmit(myOtherImage);
DataContext.Images.DeleteOnSubmit(myImage);
DataContext.SubmitChanges();

The Foreign key constraint is on OtherImages.

View 2 Replies

DataSource Controls :: Deleting Data From The Multiple SQL Tables With LINQ (C#)?

Apr 22, 2010

I'm looking for ideas on deleting data from the multiple SQL tables with LINQ (C#).

My existing solution does the following:

1. DeleteAllOnSubmit( Subset of the data from the child table)

2. DeleteAllOnSubmit( Subset of the data from another child table)

3. DeleteAllOnSubmit( Data from the parent table)

4. CommitChanges()

All this resides within a single method, which makes it ugly and not re-usable.

I have considered the following alternatives, but I decided not to use them:

1. Cascade delete on the tables.

2. Do a join on all tables from which I will remove the data, but I'm not sure yet whether I could pass a join query to DeleteAllOnSubmit().

3. Write stored procedure(s) to delete data from each table. I don't have the time to implement this due to a large number of tables and the time constraints.

View 1 Replies

ADO.NET :: Left Outer Join With Multiple Tables And Generic Lists In Linq And VB?

Nov 16, 2010

I'm having trouble doing a left outer join with multiple generic lists tables. I've been researching for hours and there isn't a whole lot out there on this subject and VB (at least what I found).

I have the following Linq query:

Dim DataForGridview = (From s In S1 _
Join c In C1 On c.ID Equals s.ID _
Join v In V1 On v.ID Equals s.ID _
Select New With { _
.dataFieldID = s.ID, _
.dataField1 = s.dataField1, _
.dataField2 = c.dataField2, _
.dataField3 = c.dataField3, _
.dataField4 = c.dataField4, _
.dataField5 = v.dataField5 _
}).ToList()

S1 has a count of 30. V1 has a count of 20. C1 has a count of 10. This query joins the three but ends up with a count of only 10 based on the datakey ID and, of course, only shows those 10 in the gridview.

I would like to show all 30 from S1 in the gridview with blank fields wherever V1 and C1 do not have data. From what I've read I need to do a left outer join. However, all examples I've found are with two datasets.


I've tried multiple times expanding on what I've found by changing it to a group join and also adding the .DefaultIfEmpty() property in the query, but nothing is working.

How can I change the above query to a left outer join with these multiple tables?

View 11 Replies

Access The Group Of A Linq Group - By Query From A Nested Repeater Control?

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

Data Controls :: Display Data From Multiple Tables Using LINQ To SQL In MVC

May 7, 2015

I have to display records from two tables where foreign key table contains many values for a primary key column how to get the data using linq query???

View 1 Replies

Databases :: Importing - Updating Sql Tables From Excel Sheet Into Multiple Tables Daily

Jan 4, 2011

I have an C# ASP application I am writing that needs to have the capability to import a generated excel or a comma delineated sheet each day. A clerk will have this job each morning so it doesn't need to be automated. My problem in trying to understand the solution to this is that the 1 sheet contains loan information, including customer information all in the same sheet. I would like to send certain columns to update information in the loan table and send other information to update the customer table. I need it to create relationships when new loans appear in the spreadsheet.

View 1 Replies

SQL Reporting :: Group Two Detail Tables In A Report With Groups?

Jul 3, 2010

I have a table with a field "item id" that I am joining to two other tables that have multiple records for each item id. In other words, I have 1 item id, 5 records in another table tied to this item and 5 records in another table tied to this item. I am trying to build a report that shows the item and then follow the item with two groupings. Each group would of course have the 5 records per the other two tables. Here is my problem. I can get the report to show an item and one of the detail group of 5 records but for the life of me I cannot get the second group of 5 to group and print. Both groups of 5 get either lumped together or it will print them 1 record at a time switching back and forth. How do you do this? Do I have to do something special in the Sql query or something.

View 1 Replies

ADO.NET :: Create SQL Into LINQ - Group By

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

ADO.NET :: Group By Date In Linq?

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

C# - SQL Group By Column Value In LINQ?

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

ADO.NET :: Get Distinct Value From A Group-Join Syntax Linq?

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

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

Exporting Multiple Tables In A DataSet To An Excel File With Multiple Sheets?

Jul 8, 2010

I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets. I found a very good article in [URL].[URL] was able to successfully export each of my datatables in the dataset to excel worksheets. This solution worked for small number of rows. But when the data got larger I am consistently getting the system.outofmemoryexception. THis is because the code is using up the memory when creating excel.

Does anyone have another option to do the above?

View 2 Replies

Data Controls :: Search In Multiple Tables Using One TextBox And Display Results In Multiple GridView

Jul 22, 2013

I created 3 product  table . A,B and c.

I show product of table in different gridview.  

Now I am using one search box. but how to search product name with image in one query all of three table.... 

Simple how to search product from multiple table and show result...

View 1 Replies

DataSource Controls :: Linq Group Cannot Get The Data Returned

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

DataSource Controls :: Order By Count And Group By In LINQ?

Mar 14, 2010

How do I translate the following query into LINQ to SQL?

[Code]....

View 1 Replies







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