Menu
SQL Server 2008 Ent ASP.NET MVC 2.0 Linq-to-SQL
I am building a gaming site, that tracks when a particular player (toon) had downed a particular monster (boss). Table looks something like:
int ToonId
int BossId
datetime LastKillTime
I use a 3d party service that gives me back latest information (toon,boss,time).
Now I want to update my database with that new information.
Brute force approach is to do line-by-line upsert. But It looks ugly (code-wise), and probably slow too.
I think better solution would be to insert new data (using temp table?) and then run MERGE statement.
Is it good idea? I know temp tables are "better-to-avoid". Should I create a permanent "temp" table just for this operation?
Or should I just read entire current set (100 rows at most), do merge and put it back from within application?
Any pointers/suggestions are always appreciated.
986
This API available since linq2db 1.9.0. It superseeds previous version of API with very limited functionality. For migration from old API check link below. Merge is an atomic operation to update table (target) content using other table (source). Merge API provides methods to build Merge command and execute it.
LINQ has a JOIN query operator that gives you SQL JOIN like behavior and syntax. The JOIN query operator compares the specified properties/keys of two collections for equality by using the EQUALS keyword. By default, all join queries written by the JOIN keyword are treated as equijoins. Let's understand the LINQ Joins using Venn diagram.
LINQ really shines when used to query remote data sources. Queries on remote data sources such as relational databases are known as integrated queries. In this article, I explore integrated queries with a SQL Server database. First I create the SQL Server database instance with Docker and then query it using LINQ.
As we know the JOIN clause is very useful when merging more than two table or object data into a single unit. It combines different source elements into one and also creates the relationship between them. Using the join, you can grab the data based on your conditions.
An ORM is the wrong tool for performing batch operations, and Linq-to-SQL is no exception. In this case I think you have picked the right solution: Store all entries in a temporary table quickly, then do the UPSERT using merge.
The fastest way to store the data to the temporary table is to use SqlBulkCopy to store all data to a table of your choice.
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
