linq-to-sql

Combining 2 Linq queries into 1

Given the following information, how can I combine these 2 linq queries into 1. Having a bit of trouble with the join statement. 'projectDetails' is just a list of ProjectDetails ProjectDetails (1 to many) PCardAuthorizations ProjectDetails (1 to many) ExpenditureDetails Notice I am grouping by the same information and selecting the sa...

How to perform a Linq2Sql query on the following dataset

I have the following tables: Person(Id, FirstName, LastName) { (1, "John", "Doe"), (2, "Peter", "Svendson") (3, "Ola", "Hansen") (4, "Mary", "Pettersen") } Sports(Id, Name) { (1, "Tennis") (2, "Soccer") (3, "Hockey") } SportsPerPerson(Id, PersonId, SportsId) { (1, 1, 1) (2, 1, 3) (3, 2, 2) (...

Business object and linq2SQL

What is the optimal way to write the code which interacts with DB using linq2SQL? I need to add some business logic to the entities. So I guess there are two ways: Write some wrapper class. The main minus is that many fields are the same, so i don't feel it as DRY style. Add business logic methods to linq2sql entities(these classes ar...

Linq to SQL: how get row security between write access??

I would like to allow two threads to write in a table at the same time (I know the problem of updating the same row, but this would be a story apart). I need that in behalf of speed up the operations in my aplication (one thread could write in row X while another could do the same in row X+n instead of waiting the first to finalize). So...

Is a full list returned first and then filtered when using linq to sql to filter data from a database or just the filtered list?

This is probably a very simple question that I am working through in an MVC project. Here's an example of what I am talking about. I have an rdml file linked to a database with a table called Users that has 500,000 rows. But I only want to find the Users who were entered on 5/7/2010. So let's say I do this in my UserRepository: from ...

Make a usable Join relationship with LINQ on top of a database CSV design error

I'm looking for a way to fix and/or abstract away a comma-separated values (CSV) list in a database field in order to reconstruct a usable relationship such that I can properly join the two tables below and query them using C# LINQ and its .Join method. Following is a sample showing the Person table and CsvArticleIds field having a CSV ...

Catch/Raise event on table data update C#

Hi, I have 24/7 service which keeps setup (configuration data) for charging, routing and etc in the Sql Server. Once it is started it loads the data from table using Linq2SQL and use the data through all the application. And we need a solution to update the setup data in the table without restarting the application. So I am interested ...

How Manage Big Linq DataContext ?

Hi The major subject in .net programs is "How manage memory for best performance". so Microsoft use garbage collector in .net and with that, we don't need to do something for managing memory(or better say we can use GC easily) But when you develop big project(business app), you make too many tables and database for your own project. s...

Using database default values with Linq to SQL codewise

I am using Dynamic Data with linq to SQL and SQL Server 2008. I have a GUID column that gets his value from the default value with newguid(). When I set IsDbGenerated to true in the designer it works like a charm. But when I renew table this property is set back to false again. So I added it to the metadata. For some reason it's not ...

Linq to Sql GroupJoin Paging oddity

I have noticed a strange Sql Translation in a LinqToSql Query I was trying to optimise. If I execute the following Recipients.GroupJoin( RecipientAttributes, x => x.Recipient_Id, y => y.Recipient_Id, (x,y) => new {Recipient = x, Attributes = y}) .Skip(1) .Take(1000) It executes in a single q...

Creating a dynamic linq query

I have the following query: from p in dataContext.Repository<IPerson>() join spp1 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp1.PersonId join s1 in dataContext.Repository<ISports>() on spp1.SportsId equals s1.Id join spp2 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp2.PersonId j...

LINQ to SQL repository - caching data

I have built my first MVC solution and used the repository pattern for retrieving/inserting/updating my database. I am now in the process of refactoring and I've noticed that a lot of (in fact all) the methods within my repository are hitting the database everytime. This seems overkill and what I'd ideally like is to do is 'cache' the m...

LINQ to SQL Foreign Key Constraint conflicts

I'm having a problem with LINQ. I have 2 tables (Parent-child relation) Table1: Events (EventID, Description) Table2: Groups (GroupID, EventID(FK), Description) Now i want to create an Event an and a child. Event e = new Event(); e.Description = "test"; Datacontext.Events.InsertOnSubmit(event) Group g = new Group(); g.Description =...

LINQ-to-SQL eagerly load entire object graph

I have a need to load an entire LINQ-to-SQL object graph from a certain point downwards, loading all child collections and the objects within them etc. This is going to be used to dump out the object structure and data to XML. Is there a way to do this without generating a large hard coded set of DataLoadOptions to 'shape' my data? ...

Passing integer lists in a sql query, best practices

I'm currently looking at ways to pass lists of integers in a SQL query, and try to decide which of them is best in which situation, what are the benefots of each, and what are the pitfalls, what should be avoided :) Right now I know of 3 ways that we currently use in our application. 1) Table valued parameter: Create a new Table Valued...

SQL & Linq To SQL Help

I have a table which is some thing like below.. Date ID 2009-07-01 1 2009-07-01 2 2009-07-01 3 2009-08-01 4 2009-08-01 5 2009-08-01 6 2009-09-01 7 2009-09-01 8 2009-10-01 9 2009-10-01 10 2009-11-01 ...

Dynamically set sproc name in Linq to SQL?

Hey, I need to dynamically set the sproc name of a Linq to SQL query and can't figure out how. I see in the dbml designer that the sproc name is an attribute but it must be a constant, so I can't set it to a dynamic value. Please help. Thanks, Justin ...

Linq generic Expression in query on "element" or on IQueryable (multiple use)

Hi, I have the following expression public static Expression<Func<T, bool>> JoinByDateCheck<T>(T entity, DateTime dateToCheck) where T : IDateInterval { return (entityToJoin) => entityToJoin.FromDate.Date <= dateToCheck.Date && (entityToJoin.ToDate == null || entityToJoin.ToDate.Value.Date >= dateToCheck.Date); } IDateInt...

Does @@identity return the primary key or identity field?

Hi, My issue is that I've got update triggers on an SQL View (MS SQL 2005) which I'm mapping to LINQ to SQL entities in C#... My SQL looks correct but it complains about trying to insert a null value into a secondary table PK field. I believe my issue relates to having the primary key and identity as separate fields in the primary tab...

LinqToSql ConnectionString won't set to "(None)"

Normally when you click in any whitespace in a LinqToSql dbml file, you can then go over to the properties window and set the Connection to (None). This is essential to me because it allows me to put the connection string in the web.config. The "(none)" option does not always show for me, especially after i make updates to the generated...