On a different note: today I learned something very interesting about LINQ To SQL (a C# .NET framework).
Given a database table
In some code at work I wanted to first run through some data extractions, calculate final statistics and then populate this table. But before the insert, I want to find all rows that currently exist that match the same Date and delete.
So what I did was as I create each new row to insert, I make a new object of that Table's type: Daily_Total.
Daily_Total dt = new Daily_Total{ ...,...,...,...};
Then I add it to a generic list of type Daily_Total
List<Daily_Total> dailyList = new List<Daily_Total>();
...(gathering data)...
dailyList.Add(dt); (repeated for every new row)
Once this is all collected, I then do a query on the table to gather those items to delete and do the deletion like such:
dbContext.Daily_Totals.DeleteAllOnSubmit(toDelete);
dbContext.SubmitChanges();
And then the weirdest thing happens that I haven't experienced yet. Not only do the rows I want to delete get deleted, but the generic list of objects of that tables type are INSERTED. No InsertAllOnSubmit(dailyList) or anything. Just simply having a generic collection of those new rows gets itself submitted.
This took me a bit to figure out why my code was throwing a "Cannot insert entry that already exists" exception. I'm going to have to research why this happens. It seems incorrect to me since I may not want a temporary, local version of my new rows to be submitted yet. Of course, I could also be doing something wrong.
That was total greek...er...geek to me. You speak an entirely different language, bro.
ReplyDeleteMy work here is done. :)
DeleteI hate to say it man but that's where a solid DBA comes in. This shouldn't be written in code but should be a stored procedure to automate that. It's faster and makes more sense to have the database execute this than do it programmatically.
ReplyDeleteUnfortunately, my team cannot afford a real DBA. Also, the complexity of the algorithm, while most likely possible in SQL, would be significant in SQL.
DeleteHave you ever used Linq to SQL in the .NET framework? It's essentially writing strongly-typed SQL. It may be marginally slower, but the power given to doing it all in code I think far outweighs any additional speed by using sprocs.
There are pros and cons to each though. it does interest me to see how possible it would be to do my automation code that the above problem existed in. but it also makes me head hurt. :)