Help ---> Select * from Inserted in Linq
-
Hi All, i am new in Linq and have a problem; i have a SQLCLR Trigger and its job is to insert the query in another DB (A) after updating it in DB (B), it is like an HistoryLog DB all Updated Queries must be logged in DBHistoy. now i have a mix Code between T_SQL und Linq in my Trigger, it works fine; i get the Datas from my Updates with: 'SELECT * FROM INSERTED' and with Linq i insert them in DBHistory, but i am not happy about this, i want to have only a linq Code inside but i didnt found an equivalent to 'Select * from Inserted' into Linq. please Help ! Thanks in advance...
-
Hi All, i am new in Linq and have a problem; i have a SQLCLR Trigger and its job is to insert the query in another DB (A) after updating it in DB (B), it is like an HistoryLog DB all Updated Queries must be logged in DBHistoy. now i have a mix Code between T_SQL und Linq in my Trigger, it works fine; i get the Datas from my Updates with: 'SELECT * FROM INSERTED' and with Linq i insert them in DBHistory, but i am not happy about this, i want to have only a linq Code inside but i didnt found an equivalent to 'Select * from Inserted' into Linq. please Help ! Thanks in advance...
Inserted/deleted are virtual tables that only exist within the context of a trigger. I cannot see how you would ever get LINQ to map to them. I am not even sure that LINQ to SQL is supported in the context of SQL-CLR
'Howard
-
Inserted/deleted are virtual tables that only exist within the context of a trigger. I cannot see how you would ever get LINQ to map to them. I am not even sure that LINQ to SQL is supported in the context of SQL-CLR
'Howard
well Linq to SQL does support SQL-CLR cause i can create this Trigger without problem and it works fine but like i said i have two Querry after an update : 1. i s_elect * from inserted_ better said from updated and after this 2. i insert them in the other database . the first querry i do it in a T-SQL way and the other Querry in Linq and the Trigger works fine like i want, but to make the performance and the maintainability better i want to make all my Queries in Linq.
-
well Linq to SQL does support SQL-CLR cause i can create this Trigger without problem and it works fine but like i said i have two Querry after an update : 1. i s_elect * from inserted_ better said from updated and after this 2. i insert them in the other database . the first querry i do it in a T-SQL way and the other Querry in Linq and the Trigger works fine like i want, but to make the performance and the maintainability better i want to make all my Queries in Linq.
Why do you think that a LINQ query will be more performant than native SQL in a trigger? I'm a bit confused about this statement, because LINQ ultimately boils down to SQL. So if you have well written SQL, it will be as fast (or possibly faster) than the LINQ equivalent.
Deja View - the feeling that you've seen this post before.
-
Why do you think that a LINQ query will be more performant than native SQL in a trigger? I'm a bit confused about this statement, because LINQ ultimately boils down to SQL. So if you have well written SQL, it will be as fast (or possibly faster) than the LINQ equivalent.
Deja View - the feeling that you've seen this post before.
-
well i think mixing it, native SQL and Linq in a Trigger maybe cost more time as only Linq or only T-SQL. do you have an idea what is 'Select * from inserted' in Linq ? Thanks for replying..
EBeylo wrote:
do you have an idea what is 'Select * from inserted' in Linq ?
Sorry - no, because I would never put LINQ in a trigger. I always do my triggers in pure T-SQL (and frankly, I didn't think it was possible to do LINQ in a trigger; you live and you learn).
Deja View - the feeling that you've seen this post before.
-
EBeylo wrote:
do you have an idea what is 'Select * from inserted' in Linq ?
Sorry - no, because I would never put LINQ in a trigger. I always do my triggers in pure T-SQL (and frankly, I didn't think it was possible to do LINQ in a trigger; you live and you learn).
Deja View - the feeling that you've seen this post before.
Pete O'Hanlon wrote: ...(and frankly, I didn't think it was possible to do LINQ in a trigger; you live and you learn). well must first create a new Library Project to get the Linq References and then i generated as an assembly in my Database and with my CLRSQL-Project which i create my Trigger i add this reference and make the linq Code inside. and it works.
-
well i think mixing it, native SQL and Linq in a Trigger maybe cost more time as only Linq or only T-SQL. do you have an idea what is 'Select * from inserted' in Linq ? Thanks for replying..
Inserted and deleted are virtual tables created by SQL in the context of a trigger. They mirror the structure of the table to which the trigger is attached and represent the data being inserted/updated/deleted. If you have a LINQ to SQL class for the parent table, e.g. Customer then you can use this to obtain SELECT * FROM Inserted thus:
var newData = dc.ExecuteQuery<customer>("SELECT * FROM inserted");However, as Pete O'Hanlon has pointed out using T-SQL for your trigger is going to be MUCH more efficient than any SQL-CLR you write (with or without LINQ). I use LINQ to SQL in my application but not on the database: I'd only use it in a trigger if there was no way whatsoever I could do it in a T-SQL statement.'Howard
-
Inserted and deleted are virtual tables created by SQL in the context of a trigger. They mirror the structure of the table to which the trigger is attached and represent the data being inserted/updated/deleted. If you have a LINQ to SQL class for the parent table, e.g. Customer then you can use this to obtain SELECT * FROM Inserted thus:
var newData = dc.ExecuteQuery<customer>("SELECT * FROM inserted");However, as Pete O'Hanlon has pointed out using T-SQL for your trigger is going to be MUCH more efficient than any SQL-CLR you write (with or without LINQ). I use LINQ to SQL in my application but not on the database: I'd only use it in a trigger if there was no way whatsoever I could do it in a T-SQL statement.'Howard
Howard wrote: If you have a LINQ to SQL class for the parent table, e.g. Customer then you can use this to obtain SELECT * FROM Inserted thus: var newData = dc.ExecuteQuery("SELECT * FROM inserted"); i tried but it throws now an SQL Exception: didnt found an Object with the name INSERTED. now i am convinced and i will make it with the native SQL. thanks
-
Howard wrote: If you have a LINQ to SQL class for the parent table, e.g. Customer then you can use this to obtain SELECT * FROM Inserted thus: var newData = dc.ExecuteQuery("SELECT * FROM inserted"); i tried but it throws now an SQL Exception: didnt found an Object with the name INSERTED. now i am convinced and i will make it with the native SQL. thanks
Doh of course it would! Well my first answer "you can't" stands correct then. You cannot use .ExecuteQuery - it would create a connection to the database using the connection string provided. How on earth one would create a connection to a trigger context I've no idea, and I think Microsoft's answer would be the same as mine - don't. And since Inserted is only visible as part of the trigger context it's never going to be visible to LINQ.
'Howard