Hi all, I need some help in how to accomplish this. What I like to do is Select all the Rows in a table that belongs to a certain ID and then Create the selected rows with the exact same data with the exception of making them all with a new ID.
So let's say my table looks like this.
Table1
------
ID - Primary Key
Name
Title
JoinID
So waht I have so far:
var theRows = from myRows in db.Table1
.Where(i => i.JoinID == passedInObj.ID)
select myRows;
Let's say my rows then looks like this from the above.
1, "Bob", "Teacher", 5
2, "Jim", "Janitor", 5
Now that I have all the Rows that belong to my passedInObj'd id. I like to then insert NEW records back into the database with the exact same info
But this time I want it with a new JoinID.
And this new JoinID is created how? It might be just an additional parameter :
nsert table1 (name, title, joinID)
select name, title, @newJoinID
from table1
where joinId = @passedID
Also it looks weird to "copy" peoples. Not sure what is the exact use case in plain English. Don't you want to "attach" the same people to another "JoinID" ?
So basically Table1 is a join table. The parent, say Table0 has a 0,1 to many relationship with Table1.
What I am doing is I will create a new record atTable0 ( and say it generated PK of 10 in the original question) this new ID will be the "joinId". I need to make a "copy" of all the child records and point it to this newly created record with ID 5.
Yeah probably but because its such a small function which won't be used heavily i need to move on to the next part of the programming. It works for now and i'll go back to optimize when I have more time. Thanks
Member
14 Points
44 Posts
MVC EF with Linq?
Mar 14, 2019 07:38 PM|MVCNewbi3v|LINK
Hi all, I need some help in how to accomplish this. What I like to do is Select all the Rows in a table that belongs to a certain ID and then Create the selected rows with the exact same data with the exception of making them all with a new ID.
So let's say my table looks like this.
Table1
------
ID - Primary Key
Name
Title
JoinID
So waht I have so far:
var theRows = from myRows in db.Table1
.Where(i => i.JoinID == passedInObj.ID)
select myRows;
Let's say my rows then looks like this from the above.
1, "Bob", "Teacher", 5
2, "Jim", "Janitor", 5
Now that I have all the Rows that belong to my passedInObj'd id. I like to then insert NEW records back into the database with the exact same info
But this time I want it with a new JoinID.
So we should have new records with
3, "Bob", "Teacher", 10
4, "Jim", "Janitor", 10
Thanks!
All-Star
50984 Points
12193 Posts
Re: MVC EF with Linq?
Mar 14, 2019 08:17 PM|bruce (sqlwork.com)|LINK
why wouldn't this be a set operation:
insert table1 (name, title, joinID)
select name, title, joinID
from table1
where joinId = @passedID
Member
14 Points
44 Posts
Re: MVC EF with Linq?
Mar 14, 2019 08:29 PM|MVCNewbi3v|LINK
That won't get me to the end state. I need a copy of the record with a new JoinID.
I noticed that i miss typed the original question - has been updated.
All-Star
43510 Points
15172 Posts
Re: MVC EF with Linq?
Mar 14, 2019 09:50 PM|PatriceSc|LINK
Hi,
And this new JoinID is created how? It might be just an additional parameter :
Also it looks weird to "copy" peoples. Not sure what is the exact use case in plain English. Don't you want to "attach" the same people to another "JoinID" ?
Member
14 Points
44 Posts
Re: MVC EF with Linq?
Mar 14, 2019 10:08 PM|MVCNewbi3v|LINK
So basically Table1 is a join table. The parent, say Table0 has a 0,1 to many relationship with Table1.
What I am doing is I will create a new record atTable0 ( and say it generated PK of 10 in the original question) this new ID will be the "joinId". I need to make a "copy" of all the child records and point it to this newly created record with ID 5.
Member
14 Points
44 Posts
Re: MVC EF with Linq?
Mar 15, 2019 12:33 AM|MVCNewbi3v|LINK
Got this to work. Just selected all and used a foreach loop to add it into the database.
All-Star
50984 Points
12193 Posts
Re: MVC EF with Linq?
Mar 15, 2019 04:14 AM|bruce (sqlwork.com)|LINK
using a for loop is a pretty bad design, this should be done as a set operation.
Member
14 Points
44 Posts
Re: MVC EF with Linq?
Mar 15, 2019 02:25 PM|MVCNewbi3v|LINK
Yeah probably but because its such a small function which won't be used heavily i need to move on to the next part of the programming. It works for now and i'll go back to optimize when I have more time. Thanks