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
Similar Messages:
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
Jan 30, 2011
I have four tables namely tblStudent, tblParent, tblEmployee and tblUnwantedVisitor.
tblUnwantedVisitor should get the primary key from the three tables, which is the visitor ID, tblStudent visitor ID starts from 10000, tblEmployee from 20000 and tblEmployee from 30000.
I used foreign key from one to one table only, so I don't know if my approach for the database is wrong or foreign key for multiple tables, which is somehow unlikely, is necessary.
View 14 Replies
Feb 23, 2011
how to insert records in multiple table using with foreign key with linq to sql?
View 2 Replies
Jan 8, 2011
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__Software__Packag__5165187F". The conflict occurred in database "StudentDB", table "dbo.Package", column 'PackageID'. The statement has been terminated.
what i try to do is run this;
[code]....
View 4 Replies
Nov 22, 2010
I'd really like to know how to do the same thing in my code as going into sql server management studio, clicking on the design of a table, and then from the file menu scrolling to the relationships and disabling the foreign key constraint by selecting no to the 'enforce foreign key constraint'.
View 2 Replies
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
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
Nov 23, 2010
I have a scenario, I will pass a StatusID , According to that StatusID , The rows will be fetched from a table[tblEventStatus] and newly fetched row has to fecth the data from another table [tblEvent].
Ex: StatusID - Value I am Passing
Table 1: tblEventStatus - Column:SysID - StatusId - EventID
Table 2: tblEvent - Column:SysID - EventID - EventDesc
I want to use the Linq - I tried like this, But It doesnt work
var query1 = from objtblEventStatus in db.tblEventStatus
where objtblEventStatus.StatusId== StatusId
join objev_events in db.tblEvent on objtblEventStatus.EventID equals
(objev_events == null ? null : objev_events.UID)
select new { Category = objev_events.EventID , Name = objev_events.EventDesc};
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
Jul 13, 2010
I have two tables with one common field. The field is the primary key in one table, and just a plain field in another. When I delete a record in the table where the common field is the primary key, I want to automaticaly delete a record in the other table that has the same data in the common field.
What is the most effecient method of doign this..triggers?
View 4 Replies
Mar 31, 2011
I cannot get the value from the drop down list and I not sure what's wrong with the declaration and the code.
Dim selectedIntake As String = intakeDropDownList.SelectedItem.Text()
Dim selectedSupervisor As String = supervisorDropDownList.SelectedItem.Text()
.supervisor_ID = selectedSupervisor(0), _
.semester_No = selectedIntake(0) _
[Code]....
This is the error message I get :
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_student_Details_lecturer_Details". The conflict occurred in database "SKTM-AAS", table "dbo.lecturer_Details", column 'supervisor_ID'. The statement has been terminated.
View 6 Replies
Apr 20, 2010
how can i retrieve the all foreign keys against a single primary key. i mean to say that if a table contain a primary key then it becomes as foreign keys in which tables.
i want to take that nam of that tables and on the base of that tables then i am to delete from all the entries. e.g: if a primary key's column entry deleted then all foreing key entries should also be deleted.
there are 988 tables in a single database. it can't be possible to go for each table to setup foreign key to ON DELETE CASCADE.
.can it have any way to delete against foreign key priority wise? mean to say that parent table contain primary key, child table contain foreign key , now there is another child table which dependent previous child table. then how can i delete all record against it?
i want to do it from programatically. i am using sql server 2005
i have completed this till to that , it retrieves all foreign keys but how to delete now on the base of priority.
[Code]....
View 1 Replies
Jun 22, 2010
I have a message table that self joins to itself where Message.ID == Message.Parent. So I have 1 Message with several ChildMessages. This is set up nicely with a Navigation property.
The code is currently:
var message = from m in soe.Messages.Include("ChildMessages")
where m.ID == id
&& m.IsActive
select m;
return message.FirstOrDefault();
What I really want is to only return 1 Message (that is equal to a particular ID and isActive) with all of its Active ChildMessages and I want to do it in one call to the database.
View 2 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
Jun 3, 2010
how i insert,update and delete data in multiple table .such tables in which primary and foreign key constraint are
View 1 Replies
Dec 15, 2010
i'll make a simple website. in my website i have two kind of products to sell
1. Projects
2. Documents
These Tables are available in my database :
[code]...
now i will make an order table. should i make differentt order table?
or i could save order information of both tables in one table?
View 6 Replies
May 3, 2010
I developed and entity framework (.edmx) application in 4.0 in that i got all the data of my querying table and its foreign key referenced tables data also. but when i change my project to 3.5 i am unable to get the data of foreign key referenced tables data.
View 2 Replies
Mar 8, 2011
I am using Entity Frameowrk as the ORM. I am new to MVC and trying to develop a samll module as a learning project.
Now I am stuck with an issue for which I can't seem to find out answer on web. The issue is :
I have two tables mapped as entities - Tenant_Definition and Contact
Tenant_Definition { Tenant_ID , Desc, Contact_ID}
Contact {Contact_ID, Company_Name,Company_Address,Person_Name, Person_Phone_Mobile}
My View has to present a form that shows Tenant Desc and Contact related fields. The index view works fine and shows the desired values.
But, the create view isn't working out. In the create view, i am planning to do -
1. creating a new contact entity instance and saving it in db
2. Then creating a new tenant entity instance using the new contact_id and save it to the DB.
However, the code doesn't seem to be inserting any value inside Contacts table. It inserts null value.Below is the code snippet of my Create Controller -
[HttpPost]
public ActionResult Create(FormCollection collection)
{
var newContact = new Contact();[code]....
View 6 Replies
