linq-to-sql

How do I use subquery, groupby, max, and top in single linqToSql statement?

Using LinqToSql, I need to return a single (L) for the most recent modDate in a join table (CL). Tables: L (Lid, meta1, meta2, ...) CL (Cid, Lid, ModDate) Here is sql that produces the expected result SELECT l.* FROM L l INNER JOIN ( SELECT TOP 1 cl.Lid, MAX(cl.ModDate) as ModDate FROM CL cl INNER JOIN L l ON cl.Lid = l.Lid AND l.m...

How do I cast a List<T> effectively?

I have a List<InputField> but I need a List<IDataField> Is there a way to cast this in c#? Or use Linq to get same result? I have two classes that implement the same interface: interface IDataField { } class InputField : IDataField { } class PurchaseField : IDataField { } This List comes from a Linq-to-Sql query: List<Input...

Linq to SQL: combine two columns to make a readonly custom column

Basically, it's the simple scenario of needing to populate a dropdown with LastName, FirstName by combining the two columns in the entity to FullName. I'd like it to live in the entity so I can grab it whenever I find a use for it. Is this possible? ...

ObjectContext memory consumption and performance

I want to write a business object layer in a way that makes each entity object responsible for saving its own changes. I thought it would be a good way to have each entity posses its own ObjectContext, attach itself to that ObjectContext and perform the transaction whenever it needs to be saved. In LINQ to SQL, DataContext is very ligh...

MVC Bulk Edit - Linq to Sql List Save

To get my head round some fundamentals using MVC and Linq to SQL I'm working on an adaption to Stephen Walther's TaskList application: I'm adding a Bulk Edit system using the concepts described in Steve Sanderson's blog. This all works as expected, however, I'm having trouble saving my returned List of Tasks. The Post to my BulkEdit lo...

MVC Bulk Edit - Examples

Ok, so this is an alternative to this question. I'm trying to produce an MVC application using LinqToSql that allows for bulk editing of data on a single page. Imagine a simple table Item with ItemId, ItemName, ItemPrice as fields. There are many examples out there of extrmely simple MVC applications that show you a list of these item...

How do I MANUALLY set an Identity field in LINQ-To-SQL (IDENTITY INSERT)

I have a table that normally, upon insert, the auto-key will increment. But, there are some instances when we want to set the ID (as would easily be done with "IDENTITY INSERT" in SQL). Is there a way to accomplish this with LINQ to SQL? Thanks, ...

Updating a disconnected LINQ object with MVC Framework RC1

This is a little out there but I have a customer object coming back to my controller. I want to just reconnect this object back to the database, is it even possible? I know there is a datacontext.customers.insertonsubmit(customer), but is there the equivalent datacontext.customers.updateonsubmit(customer)??? ...

Linq - Row not found or changed

I have the following code: Guid id = imageMetaData.ID; Data.LinqToSQL.Image dbImage = DBContext.Images.Where(x => x.ID == id).SingleOrDefault(); dbImage.width = imageMetaData.Width; dbImage.height = imageMetaData.Height; DBContext.SubmitChanges(); Looking at SQL Profiler, the following SQL is...

How do I stop the DateTimeOffset scale from causing a ChangeConflictException in linq to Sql?

The following code tries to create a new record and then modify it after it has been committed to the database. The last SubmitChanges() call throws a ChangeConflictException. ItemA itemA = new ItemA(); itemA.Foo = "a"; itemA.Created = DateTimeOffset.Now.UtcDateTime; ItemAs.InsertOnSubmit(itemA); SubmitChanges(); itemA.Foo = "b"; Subm...

How to move from LINQ to SQL to "LINQ to WCF"?

I'm putting together a WPF application which will eventually use WCF web services as its data source. During the prototyping phase, I'm using LINQ to SQL on an SQL 2008 database and have come to appreciate the ease with which I can do "Add New Item | LINQ to SQL Classes" and generate a model class for a table, then speak to it with LIN...

Linq-to-sql One-To-Many with a max

I'm having a heck of a time with this one. Might be the lack of sleep... or maybe I'm just getting dumb. I have 2 tables: a) Person {Key, Name, LastName} b) LogEntry {Key, PersonKey, Log, EntryTime} I'm trying to get a join of Person and LogEntry where LogEntry is the latest LogEntry for the given Person. I hope I don't go "duh..." i...

LINQ to SQL - select where text like string array

I have a List<string> of variable count, and I want to query (via LINQ) a table to find any items that contain any of those strings in the Text column. Tried this (doesn't work): items = from dbt in database.Items where (stringList.FindAll(s => dbt.Text.Contains(s)).Count > 0) select dbt; Query would be something li...

LINQ to SQL: Complicated query with aggregate data for a report from multiple tables for an ordering system

I want to convert the following query into LINQ syntax. I am having a great deal of trouble managing to get it to work. I actually tried starting from LINQ, but found that I might have better luck if I wrote it the other way around. SELECT pmt.guid, pmt.sku, pmt.name, opt.color, opt.size, SUM(opt.qty) AS qtySol...

LINQ to SQL: inner join with manual association on SQL 2000

I have two tables in a one to many relationship. (products and qty break pricing). At the database level I cannot create a relationship between the two tables. I brought those two tables into LINQ and created the association manually. I need to do a big LINQ query and have the tables be joined. My problem is it's not using a join to ge...

LINQ TO SQL Designer Not Working

After installing vs 2008 sp1 , linq to sql designer is not generating the code, an error message Could not load type ' r' from assembly 'System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. ...

WPF Binding relational ComboBox's from L2S with filtering on all of them.

Hi, in a WPF project with Linq to SQL, if you use the O/R - designer to create a simple structure with 3 that are all tied with forgin key relataions like so: Customer <-- Orders <-- Items, and say i want a simpe window with 3 syncronized comboboxes when you select a customer you see only his orders and when you select an Order you see...

Problem attaching an entity in LINQ to SQL

Hi, I'm trying to attach an entity in LINQ to SQL but it throws the following exceptionL: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported. <Table Name="dbo.Products" Member="Products"> <Type Name="Product"> <Column Name="Id" Type...

How do you manage your ORM layer when database undergoes changes ?

I understand the reason behind not auto refreshing your .dbml (Linq2Sql), .edmx (Linq2Entities) or .hbm.xml (NHibernate). In each of these ORM solutions you need to update these files if you have changes in the database. Is there a way to refresh these files automatically if you are 100% sure that it wont break anything? I am reasonably...

Is it possible to use output parameters with ExecuteQuery<T>?

Normally, when you want to call a stored procedure directly through Linq to Sql, you can use the ExecuteQuery method: result = dc.ExecuteQuery<MyTable>("Exec myStoredProcedure"); And if you need to call it with parameters, you can add them through string substitution: string query = "Exec myStoredProcedure "; for (int i = 0; i < para...