linq-to-sql

Difference in linq-to-sql query performance using GenericRespositry

Given i have a class like so in my Data Layer public class GenericRepository<TEntity> where TEntity : class { public MyDataContext DataContext {get;set;} [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)] public IQueryable<TEntity> SelectAll() { return DataContext.GetTable<TE...

How do I make my Linq to Sql entity expose an interface?

Using Nerd Dinner as an example: private NerdDinnerDataContext db = new NerdDinnerDataContext(); public IQueryable<Dinner> FindAllDinners() { return db.Dinners; } Is it not bad practice to directly expose the entity class Dinner here? I think it is better for the repository to return an IDinner. So my question is, how can I mak...

General SQL Server query performance

Hey guys, This might be stupid, but databases are not my thing :) Imagine the following scenario. A user can create a post and other users can reply to his post, thus forming a thread. Everything goes in a single table called Posts. All the posts that form a thread are connected with each other through a generated key called ThreadID. T...

LINQ to SQL INSERT with one to many relationship

I have two tables - Member and Profile with a one to many relationship. I'm trying to insert into the database using LINQ to SQL but am getting a "The INSERT statement conflicted with the FOREIGN KEY constraint" error. FYI: 1. As this is a practice project to learn LINQ to SQL, I've created my entity classes manually. 2. If I generate t...

Interesting LinqToSql behaviour

We have a database table that stores the location of some wave files plus related meta data. There is a foreign key (employeeid) on the table that links to an employee table. However not all wav files relate to an employee, for these records employeeid is null. We are using LinqToSQl to access the database, the query to pull out all no...

Doing a UNION ALL on two different COUNTs using Linq-To-Sql

Is it possible to write a linq-to-sql statement that will return two different COUNTs that have been put into a single dataset using UNION ALL? I know this is syntactically wrong but here's what I'm trying to do: (from t1 in TableOne select t1).Count().Union( (from t2 in TableTwo select t2).Count() ) Here's the sql I would like w...

Explicit construction of entity type [MyClass] in query is not allowed.

Hi. Like the title says, I have the following exception: Description: Event code: 3005 Event message: An unhandled exception has occurred. Exception information: Exception type: NotSupportedException Exception message: Explicit construction of entity type 'Company.Project.Core.Domain.Friend' in query is not ...

Update Entity through a new Object in LINQ to SQL

Hey Guys I'd like to update an entity via linq, but since I edit the entity in a view after serializing it, I don't have direct access to the entity inside the data context. I could do it like this: entity.property1 = obj.property1; entity.property2 = obj.property2; ... thats not cool... not cool at all. Next thing I tried is to do ...

ASP.net MVC Linq-To-SQL Many-To-Many Field Binding

Possible Duplicate: Modeling a many-to-many relationship in ASP.NET MVC using LINQ to SQL Hi there, The short version of this question is "Is there a way to gracefully handle database insertion for an object that has a many-to-many field that has been set up in a partial class?" Apologies if it's been asked before. Example S...

Removing an Entity from an EntitySet during Iteration...

I've got this code... seems nice and elegant, but apparently the framework don't like it when i mess with a collection while iterating through it: foreach (KitGroup kg in ProductToTransfer.KitGroups) { // Remove kit groups that have been excluded by the user if (inKitGroupExclusions != null && inKitGroupExclusions.Contains(k...

LINQ-to-SQL: Could not find key member 'x' of key 'x' on type 'y'

I am trying to connect my application to a SQLite database with LINQ-to-SQL, and so far everything has worked fine. The only hitch was that the SQLite provider I am using does not support code generation (unless I was doing something wrong), so I manually coded the 4 tables in the DB. The solution builds properly, but will not run, givi...

LINQ-SQL Updating Multiple Rows in a single transaction

Hi guys, I need help re-factoring this legacy LINQ-SQL code which is generating around 100 update statements. I'll keep playing around with the best solution, but would appreciate some ideas/past experience with this issue. Here's my code: List<Foo> foos; int userId = 123; using (DataClassesDataContext db = new FooDatabase()) { ...

Linq2SQL, Entities: Middle Tier or Data Access Layer

Folks, In a standard 3-tier architecture, where would you put your Linq2SQL dbml file? Is your answer the same for a ado.net entity data model? If the dbml file is in the middle tier, then do you have a data layer? Regards, Brett ...

How can I create a LINQ2SQL query that doesn't use MILLISECOND in the call to DATEDIFF?

By default if you compare two dates in a LINQ2SQL query, the resulting SQL will be DATEDIFF(MILLISECOND, .....) which requires using BIGINT as well and usually some CONVERT calls depending on what you're doing. As an example, try looking at the SQL output if you write (DateTime1 - DateTime2).Days It's a mess! I would just like to ...

Switch statement in compiled query possible?

Is it at all possible to use something like a switch statement in a compiled query for linq to entities/sql? For example when returning sorted records from the database, I would like to use a switch-like statement within one compiled query to sort on different properties, instead of having to write 2 compiled queries (ascending & descend...

Execute Command and Now()

DataContext.ExecuteCommand("DELETE from Table WHERE Date < Now()"); I get an error about how Now() is not a recognized built in function name. ...

How to Update with LINQ?

currently, I'm doing an update similar to as follows, because I can't see a better way of doing it. I've tried suggestions that I've read in blogs but none work, such as http://msdn.microsoft.com/en-us/library/bb425822.aspx and http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx Maybe these do work and I'm m...

change linq query result value's after performing queries, filters and so on

hi, i want to change record's in result of linq query. how? my query : var results = (from myTable in db.MyTable where //some condition here orderby myTable.Count descending select new QueryResult() { Title = files.Title, ...

How do I tell if a linq to sql object is new, modified or unchanged?

I have a single linq to sql class. I would like to detect if it is new (meaning an insert will be done) or if there are any pending changes (update will be done). I realize that I can do this by using a partial class and hooking into the on change events for each property. However that is a lot of maintenance for a class that is const...

Inspection of Insert Statement When Using LINQ's SubmitChanges

Hi. I want to see what my insert statement would look like as if I was wiring up an text-based ADO.NET command. How do I do this? I have been following the below link: http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-writers And have added the DebugTextWriter class to my project. So, now, i...