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
Similar Messages:
Mar 23, 2011
I have two tables:
Entity
ID (PK), int
Name
Descrip
Users
ID (PK)
EntityID, int (this is not connected to Entity table)
Now I am using LINQ to pull the records which has a Entity.ID = something. Which will show me couple of records in my GridView. Here is my LINQ statement:
protected void Page_Load(object sender, EventArgs e)
{
string getEntity = Request.QueryString["EntityID"];
int getIntEntity = Int32.Parse(getEntity);
OISEntityTestingDataContext db = new OISEntityTestingDataContext();
//OISLinqtoSQLDataContext db = new OISLinqtoSQLDataContext();
var tr =
from r in db.Users
join s in db.Entities on r.UserID equals s.ID
where s.ID == getIntEntity
select new
{
//To Show Items in GridView!
};
GridView1.DataSource = tr;
GridView1.DataBind();
}
Now here I am getting an error mesg on 'join' The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join What does that mean?
View 3 Replies
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
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
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
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
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
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
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
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
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
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
May 8, 2010
how to write that below query in linq
DECLARE @Name varchar(20)
Select @Name = empFirstname+','+empLastName+','+empBankCode From srpEmployee
PRINT @Name
View 7 Replies
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
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
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
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
Nov 8, 2010
using sql server 2005 and vb.net 2005. I am writing a console application to import a mass amount of data from a non-sql database table into a sql server database table. The destination db table (sql server) has fewer tables than the source db table so I need to map the columns. does anyone have an example of how to do this?
View 1 Replies
Dec 3, 2010
is there an easy way to compare two columns in two datatable to see if they match? I need to check if the values in column["x"] of datatable1 occur in column ["y"] of datatable2, without iterating through both tables (for each value in column of dt1, check column of dt2). I tried:
foreach (DataRow dr in dt1.rows)
if (dt2.rows.find(dr["x"]) but this checks the whole row and if finds an "x" in any column, "if" condition is true. I need to only check one column of dt2. Tables have different columns but the same primary column and that's what I need to check
View 10 Replies
Sep 28, 2010
What would you all suggest a database with less number of tables and more number of columns or more number of tables with less number of columns. I am developing a web application using Visual stuio 2005 and Sql Server 2005
View 2 Replies
Jun 15, 2010
I want to know that how many tables we can have in database and what is the maximum no. of columns.I also want to know the maximum size for each datatype.
View 3 Replies
Apr 27, 2010
I need to join some tables to get the matching records. I often have to ask around to see how certain tables could be joined. Is there any way to query the information schema or some thing else to see what columns/values match in certain tables in order to figure out how tables should be joined.
View 4 Replies
Jul 14, 2010
I have two sql tables.
- (Fields:PersonID, FirstName, LastName, Role, Department)
and
- (fields: PersonID, Unit).
I want to merge those with a single table ( Fields:PersonID, FirstName, LastName, Role, Department, Unit). How can I query to accomplish this so that same personID data go to same row ? ANY QUERY ?? (I have an idea : both export to excel and merge into a single excel file and import to SQL table but due to very long data it was truncated last time when importing so I don't want to reimport again.)
View 5 Replies
Jan 13, 2011
I have a .net code that generate a nested grid kind of output. I have written the logic to to generate the nested grid in prerender. The use html tables to generate the same. I need to align all the columns in the grid.
How can I align the columns of nested tables for the following HTML code. I want all the columns from all nested tables to be aligned. I will use that logic then in my prerender.
[Code]....
View 2 Replies
May 7, 2015
I want search with multiple names in Name coloumn. example I have pass the criteria to Name cloumn is 'Atul', 'Mayur', 'Suraj' this three names how to pass in a query.
View 1 Replies
