linq-to-sql

LINQ: Count number of true booleans in multiple columns

I'm using LINQ to SQL to speed up delivery of a project, which it's really helping with. However I'm struggling with a few things I'm used to doing with manual SQL. I have a LINQ collection containing three columns, each containing a boolean value representing whether an e-mail, mobile or address is availble. I want to write a LINQ que...

LINQ-to-SQL - C# - OnValidate()

Hello, I have a system with 3 layers and I am using LINQ-to-SQL to persist. I want to validate if the description field of my object is empty. I am using partial classes and the method OnValidate(). If the field is empty, it throws an exception. Is this correct? What do I do after the exception to not close the form, letting the user...

Linq to SQL gives NotSupportedException when using local variables

It appears to me that it matters whether you use a variable to temporary store an IQueryable or not. See the simplified example below: This works: List<string> jobNames = new List<string> { "ICT" }; var ictPeops = from p in dataContext.Persons where ( from j in dataContext.Jobs where jobNames.Con...

TransactionScope works in some places and not in others

Using ASP.NET 3.5, Linq to SQL, SQL Server 2005 on Windows Server 2003. Running VS 2008 on XP SP3 locally. We need to be able to wrap inserts, updates, and deletes in a transaction. When we first tried this by wrapping code blocks with using(var trans = new TransactionScope()) { ...; trans.Complete(); }, we got an appropriate exception ...

is there a better way to write this frankenstein LINQ query that searches for values in a child table and orders them by relevance?

I have a table of Users and a one to many UserSkills table. I need to be able to search for users based on skills. This query takes a list of desired skills and searches for users who have those skills. I want to sort the users based on the number of desired skills they posses. So if a users only has 1 of 3 desired skills he will be furt...

Linq 2 Sql DateTime format to string yyyy-MM-dd

Basically, i need the equivalent of T-SQL CONVERT(NVARCHAR(10), datevalue, 126) I've tried: from t in ctx.table select t.Date.ToString("yyyy-MM-dd") but it throws not supported exception from t in ctx.table select "" + t.Date.Year + "-" + t.Date.Month + "-" + t.Date.Day but i don't think it's an usable solution, because i might need t...

What's faster? select [object] or select new {[object].value1, [object].value2} with linq

I was hoping some of you guru's out there know the answer to this question. Which is faster? A or B A: var item = from d in database select d; B: var item = from d in database select new {d.item1, d.item2}; SEEMINGLY, it seems like selecting part of the table object would be faster than selecting the entire o...

return from a linq where statement

I have the following link function MyLinqToSQLTable.Where(x => x.objectID == paramObjectID).ToList(); I most of the time you can change a linq call to be several lines by adding curly brackets around the method body. Like this: MyLinqToSQLTable.Where(x => { x.objectID == paramObjectID; }).ToList(); Problem is the implied retu...

Linq@sql Is it possible to excute sql query with Linq2Sql for a type not defined in codegen

Hi, I have an app and I have come to a point where I need to build a sql statement up dynamically. Is there a way to execute it and return a type that has not been defined via the designer with Linq2Sql?? Malcolm ...

How to check results of LINQ to SQL query?

In a WPF app I'd like to check if a return of a LINQ to SQL query contains some records, but my approach doesn't work: TdbDataContext context = new TdbDataContext(); var sh = from p in context.Items where p.Selected == true select p; if (sh == null) { MessageBox.Show("There are no Selected It...

Get sum of two columns in one LINQ query

Hi, let's say that I have a table called Items (ID int, Done int, Total int) I can do it by two queries: int total = m.Items.Sum(p=>p.Total) int done = m.Items.Sum(p=>p.Done) But I'd like to do it in one query, something like this: var x = from p in m.Items select new { Sum(p.Total), Sum(p.Done)}; Surely there is a way to call agg...

There is already an open DataReader associated with this Command error in Linq To sql

I am getting the above error when i retreived the data from the Datacontext Model. it is happening now and then also. ...

scope identity with linq

How do you a procedure like scope identity with linq want to use it in the TODO statement protected void btnAdd_Click(object sender, EventArgs e) { if (txtZip.Text != "" && txtAdd1.Text != "" && txtCity.Text != "") { TestDataClassDataContext dc = new TestDataClassDataContext(); Address add...

How can I stop an auto-generated Linq to SQL class from loading ALL data?

DUPLICATE of http://stackoverflow.com/questions/2433422/how-can-i-stop-an-auto-generated-linq-to-sql-class-from-loading-all-data post answers there! I have an ASP.NET MVC project, much like the NerdDinner tutorial example. (I'm using MVC 2, but followed the NerdDinner tutorial in order to create it). As per the instructions in part 3...

How can I stop an auto-generated Linq to SQL class from loading ALL data?

UPDATE: from what I'm hearing, I was imagining the problem I describe below. So, this is pretty much a non-question. Future readers, move on... nothing to see here. I have an ASP.NET MVC project, much like the NerdDinner tutorial example. (I'm using MVC 2, but followed the NerdDinner tutorial in order to create it). As per the instru...

LInq to SQL - Partial Class - C#

Hi, I have a system with 2 different projects, one is called LINQ_Extensions and the other is ORM_Linq. On ORM_Linq i have the LINQ diagram with the SQL tables "converted" in clases. One of the Class is called "Tipos_Pago" In the other project i have another class (partial class) "Tipos_Pago". I want to use the method OnValidate to val...

LINQ-to-SQL IN/Contains() for Nullable<T>

I want to generate this SQL statement in LINQ: select * from Foo where Value in ( 1, 2, 3 ) The tricky bit seems to be that Value is a column that allows nulls. The equivalent LINQ code would seem to be: IEnumerable<Foo> foos = MyDataContext.Foos; IEnumerable<int> values = GetMyValues(); var myFoos = from foo in foos wh...

Linqbuilder Query with an OrderBy

I have a 1 : M relationship. I built a dynamic query based on input from users to return the listing of parents entities along with their children (using predicate builder: (done successfully new TDataContext().Ps.Where(predicate) )... but need to order the results by a field found only on the child entities. I'm at a loss: new TDat...

Using conditionals in Linq Programatically

I was just reading a recent question on using conditionals in Linq and it reminded me of an issue I have not been able to resolve. When building Linq to SQL queries programatically how can this be done when the number of conditionals is not known until runtime? For instance in the code below the first clause creates an IQueryable that, ...

linq to sql string property from non-null column with default

I have a LINQ to SQL class "VoucherRecord" based on a simple table. One property "Note" is a string that represents an nvarchar(255) column, which is non-nullable and has a default value of empty string (''). If I instantiate a VoucherRecord the initial value of the Note property is null. If I add it using a DataContext's InsertOnSubm...