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?
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?
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.
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?
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
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
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.
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.
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.)
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.
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.
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.
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...").
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.
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.
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?
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();
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?
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: