ADO.NET :: Linq To Sql Determine Which Tables To Write?
Sep 9, 2010
I was wondering how Linq to Sql determines which tables to write to when given primary/foreign Key relationships. I can (hopefully) better explain using an example. I have the following tables:
Therefore I have a Customer Entity, a CustomerStatus entity, a CustomerType Entity and a List of CustomerAddresses. In my code, I load up the Customer Entity and the List of CustomerAddresses because that's all I need. For some reason Linq to Sql is trying to insert a new Customer Status record. I have no idea why its doing this.
Can anyone please explain to me how linq to sql determines which table(s) will be written to based on the entities above?
I'm writing a SQL query where I need to detect if the value of an integer column in one table is contained within the value of a varchar column of another table. For example:
Table1 has columns A, B, C
A = 1
B = 2
C = 3
Table2 has columns D, E, F
D = "1,2,3,4,5"
E = "2, 5, 10, 15"
F = "1, 3, 49"
How would I write SQL SELECT statement to determine if the value in Table1.A is in Table2.D, Table1.B is in Table2.E, and Table1.C is in Table2.F?
List<ThemeObject> themeList = (from theme in database.Themes join image in database.DBImages on theme.imageID equals image.imageID into resultSet from item in resultSet select new ThemeObject { Name = theme.Name, ImageID = item.imageID}).ToList(); dgvGridView.DataSource = themeList; dgvGridView.DataBind();
The list object populates fine. The datagrid is setup with 2 columns. A textbox column for the "Name" which is bound to "Name" An image column which is bound to the "ImageID" field. When I execute the code I receive the following error on the DataBind() Could not determine a MetaTable. A MetaTable could not be determined for the data source '' and one could not be inferred from the request URL. Make sure that the table is mapped to the dats source, or that the data source is configured with a valid context type and table name, or that the request is part of a registered DynamicDataRoute. I'm not using any dynamicdataroutes as far as I can tell. Has anyone experienced this error before?
how to use a LINQ query in the following scenario and if we can do it without any enumerators:Suppose we have an Employee table and a Dept table where we have DeptId as a foriegn key reference in Employee table. So we can write a SQL like this.SELECT * FROM Employee WHERE DeptId in (SELECT DeptId FROM Dept WHERE Location = 'LK') Here, in the where clause, we might get multiple values. Is it possible to pass a list of values to the where clause in Linq?
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.
string[] ids=new string[]{"1","...","x"}; var result=db.table0.AsQueryable(); foreach (string id in ids) { result=result.Where(p=>p.patentId==int.Parse(id)); } I need the result is OR, not AND,
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 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?
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 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:
The main problem is that I recieve the following message:
"base {System.SystemException} = {"Unable to create a constant value of type 'BokButik1.Models.Book-Author'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}"
based on this LinQ code:
[Code]....
How shall I solve this problem to get a class instance that contain with property title and Book-AuthorID?I also have tried making some dummy by using "allbooks" relation with Code Samples from the address
http://www.hookedonlinq.com/JoinOperator.ashx. Unfortunately, still same problem.
I also have taken account to Int32 due to entity framework http://msdn.microsoft.com/en-us/library/bb896317.aspx. Unfortunatley, still same problem.
Using database with 3 tables and one of them is a many to many relationship. This database is used in relation with entity framework
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?
I'm working with asp.net 3.5 WebForms and LINQ to Entities.
I created the database with their relationships, but when I go to import the data with LinqToEntities, some reports and have not seen any reports 1 to 1.
I can create them after the reports have imported the tables LinqToEntities?
I have something like the following in pages in two different projects, which saves to a DB with a LINQ to SQL operation:
[Code]....
One of the pages works with this code and the other doesn't. The only difference is that the project in which it does work employs a data control tool (ListView) and a custom profile provider.
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.
