linq-to-sql

Looking for guidance on using LINQ-to-SQL

I am part of a team developing a windows application. We have decided to use LINQ-to-SQL. We are using the designer to generate the classes. There are few things I would like to know Do you load some data (master), and keep it in memory? Are you using DDD principles? I would like to know how you have handled various scenarios in a...

Linq to SQL With Left Outer Join and Group By With Sum - How To

Hi ! I'm trying to transform the SQL Query below into Linq to SQL select Categorias.IdCategoria, Categorias.Nome, SUM(lancamentos.valor) from lancamentos left outer join Categorias on Lancamentos.IdCategoria = Categorias.IdCategoria where Month(DataLancamento) = 11 and Credito = 1 and Lancamentos.Ocultar = 0 group by Categorias...

Extending IQueryable to return objects where a property contains a string

I see a lot of code similar to the following var customrs = MyDataContext.Customers.Where(...); if (!String.IsNullOrEmpty(input)) { customers = customers.Where(c => c.Email.Contains(input)); } I would like to put this in an extension method that checks the input is valid before invoking Where on IQueryable so that it can be called l...

Returning unread records using Linq To SQL

I'm not sure how to ask this question, so I'll start with an example of what I'm doing. Here is my table structure... Documents (Primary key = ID) ID, Title, LatestApprovedRevID Revisions (Primary key = ID) ID, DocumentID, RevisionNum, Body Document_Reads (Primary key = DocumentID, UserName) DocumentID, UserName, RevisionID When a u...

Repository design problems

Hi everybody! I think i have some design errors in my ongoing web project (i'm using Linq2SQL implementing repository pattern) 1) Every repository creates its own DataContext (is this correct?should this be this way) For example: public class SQLPersonRepository : IPersonRepository { DataContext dc; public SQLPersonRepository(s...

Using a schema other than 'dbo' in LinqToSql Designer

Is there a way to specify in a data connection or the LinqToSql Designer a schema? Whenever I go to setup a data connection for LinqToSql there doesn't seem to be anyway to specify a schema and I get 'dbo' by default. I figure that I can't be the first person to use a schema other than 'dbo' with LinqToSql, so I must be missing somet...

ASP.NET MVC Forum Application

I need to write a forum application for a friend's web site. I want to write it in C# 3.0 against the ASP.NET MVC framework, with SQL Server database. So, I've two questions: Should I use Linq to SQL or the Entity Framework as a database abstraction? Should I use the ASP.NET Membership API or add Users table and implement it myself? ...

how does linq2sql keep track of database objects?

Hi, When using Linq2sql everything automagically works. My experience is that going with the flow is not always the best solution and to understand how something internally works is better so you use the technique optimally. So, my question is about linq2sql. If I do a query and get some database objects, or I create a new one, someho...

what is the easiest way to have multiple db connections when using linq

Optimally I would like linq to use X database connections instead of 1 to speed things up. The only way now is to open X connections, and create X databasecontexts, and associate them. It would be easier if somehow I could tell linq to use X connections. Is this possible? Or is there another way to speed up database queries? thanks e...

LINQtoSQL: What is the best way to check if a row exists on insert?

Hi, I'm doing updates from Excel/CSV files to a database. I'm using LINQ to SQL for database operations. I have quite a few columns to update/insert, so I guess it would be easier to generate a Checksum somehow for the whole row. For example I can have the same person [firstname,lastname,ssn,address] in the destination table, BUT sent ...

LINQ to SQL Dynamic Sort question

How do I code the Select clause in my LINQ satament to select column aliases so I can sort on them basically I want to accomplish this SQL statement in LINQ: select type_id as id, type_desc as description from dbo.equip_type_avt order by description What do I replace the ????? in my .Select clause in my LINQ stat...

Generic Table<TEntity>

Hi everyone, Trying to use Linq to SQL for a small project I'm working on at home. I have generated a the context code and all my entity classes using dbmetal.exe (from the DBLinq project) against a local MySQL database. Everything is working great, but I'm trying to abstract some redundant code and I'm running into issues trying to do...

Simple math question with LinQ, simple Join query, Nulls, etc.

I have 2 tables   1. Client   2. Operations Operations can result in: Credits or Debits ('C' or 'D' in a char field) along with date and ammount fields. I must calculate the balance for each client's account using linQ... The result should also show balance 0 for clients whom didn't make operations yet I have the following function wi...

LINQ: DataContext Life and System.Data.SqlClient.SqlException: Timeout expired.

Hi there, i seem to be getting a lot of this in my Linq 2 SQL System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. There is really no reason for it, its a simple query that returns 1 record. I was thinking about opening my datacontext w...

UNITY: passing in a new datacontext each time?

Hi there, I am trying to use unity to automatically inject a datacontext on my repository using a new instance each time.., my idea is the ensure that each time a new datacontext is injected Currently its failing on creating the repository, i think it can't resolve MyDataContext Before creating a constructor on "the repository" (see b...

Generating very large XML file with Linq-to-XML and Linq-to-SQL

I'm trying to do a dump to XML of a very large database (many gigabytes). I'm using Linq-to-SQL to get the data out of the database and Linq-to-XML to generate XML. I'm using XStreamingElement to keep memory use low. The job still allocates all available memory, however, before keeling over without having written any XML. The structure l...

Linq To SQL: Table relationships

Assume I have a two tables, A and B. Table A has a primary Key called A_ID of type int, and table B has a foreign key called A_ID. When I add the two tables to a Linq To SQL data context class it correctly creates the classes and the association between them. My question is, class B will have a property of type int called A_ID. I...

Unity: Using same datacontext in application_BeginRequest?

Hi there, Previously i managed to setup my unity to provide a new DataContext each time to my Repository project. It works great. But for example, I am in a WCF method which opens up 2 services which in turn opens up 2 repositories (repository pattern).. i was hoping to be able to reuse the same datacontext within the same wcf method....

LINQ to SQL classes to my own classes

I'm looking at using LINQ to SQL for a new project I'm working on, but I do not want to expose the LINQ classes to my applications. For example, do an select in link returns a System.Linq.IQueryable<> collection. As well, all the classes generated to represent the database use Table, Column, EntityRef classes and attributes. It's fine...

LINQ to SQL - custom function

I would like to run a LINQ query like this: var words = from p in db.Words where p.Document.Corpus.Name == corpus //where LevenshteinDistance(p.Text.ToCharArray(), word.ToCharArray()) < threshold select p; But if I place the "LevenshteinDistance" function in there it will generate an error: NotSupportedException: Method '...