I'm writing a script to check RSS feeds on regular intervals and need to prune out old articles. I came across this answer which seems really close to what I'm looking for: http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n I need a similar solution that works the same way, except it k...
I have a User table, Roles table and a link table User2Roles. I want to create LinqToSQL entities so that I can access roles from user as role.Users and user.Roles manually. Using designer it actually creates 3 entities, User to User2Roles and User2Roles to Roles. ...
Hi, I'm using Linq to SQL and read in a blog post about closing database connections as soon as possible. As an example, they showed a variable being converted to a list (using .ToList()) instead of actually returning the Linq query. I have the below code: public static bool HasPassword(string userId) { ProjDataContext db = new...
Hi, Ive got the following code which I hacked together from website examples. It works nicely but I dont really understand how it works... public static Func<EuvaTransientDataContext, string, string, int, IQueryable<SecurityAudit>> MatchedIPAddressesAuditRecords = CompiledQuery.Compile((EuvaTransientDataContext db, string us...
I am using Linq to SQL and have created a series of repositories to wrap table operations. The data context has been wrapped as well so that we can control when a data context is created / destroyed (since we are working in ASP .Net we are using one context per request). We have a number of scalar functions that return 4 character codes ...
Apologies if this is a duplicate question, but is there a real difference between executing a SQL statement in Linq2Sql compared to executing a Stored Procedure? What are the benefits? (if any) ...
We use queries generated by Linq for data retrieval but for INSERT and UPDATE we do not allow generated SQL, but restrict to the use of stored procedures. I connected the Update and the Insert behaviour in the DBML to the stored procedures. The procedures are called, the data gets inserted/updated = all if fine, except in the case of o...
In a Rob Conery-style ASP.NET MVC application, you typically have a repository: public class CustomerRepository { DataContext dc = new DataContext(); public IQueryable<Customer> AllCustomers() { return db.Customers; } public Customer GetCustomer(int customerID) { return db.Customers.FirstOrDefau...
I have the following which works in SQL Query Analyzer. select oh.* from order_history oh join orders o on o.order_id = oh.order_id where oh.order_id = 20119 and oh.date_inserted = ( select max(date_inserted) from order_history where order_id = oh.order_id group by order_id ) How would I go about converting to LINQ? From test ...
Given a table of order items (OrderItems) that relates to a table of orders (Orders), which in turn relates to a table of users (Users), is it possible to retrieve all the OrderItems and group them into a dictionary by OrderId with just one query? ie. without performing an iteration over either the OrderItems result set or performing a q...
Let's say I created a new product like this: Product p=new Product(){ Id= 2,Name="some name"}; My product variable has never been attached to a data context, how can I attach this entity so that my existing product in the database with Id=2 gets update with the name of my detached product? ...
In a linq2sql class, I call a stored procedure that returns multiple result sets and it works. Whenever I add a new procedure in the linq2sql designer and it stealth converts the aforementioned stored procedure from IMultipleResults to ISingleResult in designer.cs. I replaced the code with a previous version and it works, but why do...
consider the following code, which represents an attempt to implement partial matching. the intended result is a row for any 1 or more fields that match between the query entity and the data store. so if you supply person.email we want a match against that, if you supply person.email and person.FirstName we should filter the results fur...
Hi guys, I have a database that is accessed by two applications. The first is an ASP.NET MVC application which just reads data out of the database. The second is a C# Console application which reads and writes to the database. The problem I'm experiencing is that when the console application performs a mass update of up to several hund...
I have a D object created from the automatic mapping of the D table. I added the following property to it in a partial class. public Address PhysicalAddress { get { return this.Addresses.FirstOrDefault(a => a.AddrType == "PHY"); } } This works fine on it's own. I'd like to write the following linq query on it: var result = from ...
This fails on VS2010 RC LINQ-to-SQL with the InvalidOperationException "Stored procedures cannot be used inside queries.": var foo = (from a in aTable from b in this.SomeStoredProcedure() where a.Id == b.Id select b.Id); SomeStoredProcedure is a SQL procedure which returns a table. 'join' also appears to fail. Any thought...
This query (or rather one similar to it) is in a codebase we have already deployed. var timeblocks = from tb in DB.TimeBlocks where tb.StartDate.Date.AddDays(1) <= DateTime.Today select tb.Id; DB is a datacontext that connects to the database. TimeBlocks is a fairly simple table, StartDate is a DateTime column. Currently the...
I am having trouble with inheritance mapping in Linq to Sql. I am using MSDN as a reference and as a basis it sounds good. However the example it gives is a single table inheritance mapping. However, I am trying to do multiple table inheritance to save on table space. Is this possible? So far I have: [Table(Name="writing_objs")] [I...
Dears, I have a table like this in my Linq to sql class : ID CL1 CL2 CL3 ... CL20 -- ---- ---- ----- ------ 1 12 35 54 .... 44 2 11 35 78 ..... 75 data is not important in this example. I need to access to each column with their index. for example to reach data in CL3 like this: var x...
I'm just starting to look into using LINQ for my database (and XML, and data object!) needs, but need to decide on which database to go with. I've been reading Pro LINQ, and it says that currently, LINQ in .NET 3.5 only supports SQL Server. I have done some googling, and have found references to using LINQ with MySQL and PostgreSQL (my...