C# - Pulling Out Different Columns From Tables Using LINQ
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
Similar Messages:
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
Apr 8, 2010
In my application I'm pulling back a user's "feed". This contains all of that user's activities, events, friend requests from other users, etc. When I pull back the feed I'm calling various functions to filter the request along the way.
var userFeed = GetFeed(db); // Query to pull back all data
userFeed = FilterByUser(userFeed, user, db); // Filter for the user
userFeed = SortFeed(userFeed, page, sortBy, typeName); // Sort it
The data that is returned is exactly what I need, however when I look at a SQL Profile Trace I can see that the query that is getting this data does not filter it at the database level and instead is selecting ALL data in the table(s).
This query does not execute until I iterate through the results on my view. All of these functions return an IEnumerable object.
I was under the impression that LINQ would take all of my filters and form one query to pull back the data I want instead of pulling back all the data and then filtering it on the server.
View 2 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
Jun 30, 2010
Dim AlterTable As String = "alter table [Upload] add [Country] nvarchar (50) default 0 NOT NULL,[Address] int default 0 NOT NULL"
Dim connection As Data.SqlClient.SqlConnection
connection = New Data.SqlClient.SqlConnection()
connection.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBconnectionString1").ConnectionString
Dim cmdUpdating1 As New Data.SqlClient.SqlCommand(AlterTable, connection)
cmdUpdating1.CommandType = CommandType.Text
connection.Open()
cmdUpdating1.ExecuteNonQuery()
connection.Close()
This is the code which I am using for altering the number of columns in a table in the DB. so with this code i am able to add new coulmns dynamically. Now my major requirement in this is to obtain the headercount from the user( say "telephone number" and "language") and based on it generate the columns dynamically in the table in the DB.
View 12 Replies
Feb 4, 2010
I have 2 tables and I want to entries of the tables into something like this:
Table 1: Table 2: End Result:
a 1 a - 1
b 2 a - 2
... ... b - 1
... ... b - 2
... ... ...
View 3 Replies
Sep 15, 2010
I am developing the Home page of a Client. Apart from various things on this home page I have a text box and search button. Having said that, I have a database in which I have almost 12 tables with varying number of columns. Now my question is,Is there anyway to search a word typed by a user in the textbox to search it in all the tables(all columns) of the database.
View 2 Replies
Jan 12, 2011
How to create a table which is the combination of different columns in database tables.
I know how to make the var table using single table.
What is I have to combine multiple coolumns and create a table.
My requirement is a gridview which has a datasource which is loading from columns from multiple tables.
View 13 Replies
Feb 9, 2010
I am trying to create an application in which there is a summary of outstanding payments.
One table is Table_Bills.
The other table stores records of Payments made and is called Table_Payments.
In summary i want to show 3 figures - each in separate one field datagridview with a data_source in separate data_adapter:
1) Ammount_Due 2) Amount_Paid 3) Balance.
The first two are no problem but the last is giving me a headache.
I tried this:
SELECT SUM(DHB_New_Jobtable.DayWage) - SUM(DHB_Payments.Ammount) AS Expr1
View 4 Replies
Feb 17, 2010
I have two SQL Server tables with Primary Keys (PK) and a Foreign Key (FK) linking the two tables:
1) Table "Order"
OrderID, int, PK
AddressID, int, FK
...
2) Table "Address"
AddressID, int, PK
City, nvarchar(50)
...
Then I've created an (ADO.NET) Entity Data Model out of those two tables. Now on my (ASP.NET) webpage I put a GridView with an EntityDataSource. In my GridView I want to display two columns:
OrderID City (belonging to that order and linked by the AddressID-key) How can I do that? My problem is: When I configure the Entity Data Source I can select an "EntitySetName" which can be either "Order" or "Address" but not both, nor can I select any kind of relationship. If I select "Order" as EntitySetName
then in the GridView I can add the columns
OrderID
Address
Address.AddressID
Adding the column "Address" displays empty cells. Adding "OrderID" and "Address.AddressID" displays the expected IDs. But how can I add the "City" of the related address to my GridView? Edit: Clarification:
The Entity Framework has created a class "Order" and a class "Address" corresponding to the database tables. The class "Order" has a reference to an "Address" object as a navigation property, corresponding to the 1-n relationship between Address and Order table. Basically I want to have a column in my GridView which displays Order.Address.City. I have tried to add a bound field with "Address.City" as data field to the GridView but it results in a runtime error ("no such property...").
View 3 Replies
Apr 1, 2011
I want to hide all FK Columns in all tables. I am using Asp.Net Dynamic Data 4.0.
I tried to use this code:
[Code]....
And then in List page template call:
[Code]....
But this does not work. If I set breakpoint in function GenerateFields it newer stops there.
View 1 Replies
Jul 14, 2010
I want to know the procedure of binding a gridview. but the condition is that thefre are three columns in this gridview. if I wud bind it from one table then it wud ok for me. but now I need to bind it from three different tables. onw column from one table, another from 2nd.. and so on.
View 5 Replies
Jan 24, 2011
I need to make a category filter. For this purpose I have two tables "Ingredient" and "Category"
My Category Table looks like this:
[Code]....
And my ingredient table:
[Code]....
I want to join the two tables so when the KategoriID match it should then return all from that category.
[Code]....
[Code]....
View 2 Replies
Sep 9, 2010
I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:
SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17';
can be expressed as:
var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 };
But how would I join 3 or more tables using LINQ? For example:
SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37';
I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement.
View 4 Replies
Sep 4, 2010
I am using VS 2010 I've created LiNQ .dbml file via .. Model>Add New Item>Data>LINQ to SQL Clases I've put table on designer surface using drag & drop. I've done all this from one tutorial. There Is Mapping with arrows between tables in tutorial. How can i do this?
View 2 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
Nov 23, 2010
I am very new to Linq , i want to check if the PrintingAdminSetting table has rows in it and then if it has then i want to get the value and assign it to txtMaxJobs textbox
below is teh code
, please let me know teh syntax to check .
var DC = new ServiceDataContext();
var rec = DC.PrintingAdminSetting.Where("").First();
txtMaxJobs.Text = rec.ParaName.ToString();
View 4 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
Nov 9, 2010
Here's my DB structure:
I have three tables.
User, Details, Optional
User has fields: ID, FirstName, LastName
Details has fields: ID, MyDetails1, MyDetails2, User_ID
Optional has fields: ID, MyOptions1, MyOptions2, User_ID
There is a 1 to many relationship with User & Detail (User ID & Detail User_ID)
There is a 1 to many relationship with User & Optional (User ID & Optional User_ID)
With that said, one user can have many details and/or many optionals
Now I'm trying to build a linq query that will say:
"where user id = x, select FirstName, LastName, all MyDetails1 from every record where User_ID == x, all MyOptions1 from every record where User_ID == x"
After the merge I should have one string that lists the distict user & all of their many fields:
FirstName + LastName + (MyDetails1 + MyDetails1 + MyDetails1..etc) + (MyOptions1 + MyOptions1)
Anyone try this before?
I tried this with JOIN but this doesn't do the trick for what I'm looking for.
So if I had 3 MyDetails records associated with ID == 2, I would get three results like:
John Doe MyDetails_X
John Doe MyDetails_Y
John Doe MyDetails_Z
View 1 Replies