linq-to-sql

Linq to SQL nvarchar problem

Hi, I have discovered a huge performance problem in Linq to SQL. When selecting from a table using strings, the parameters passed to sql server are always nvarchar, even when the sql table is a varchar. This results in table scans instead of seeks, a massive performance issue. var q = ( from a in tbl where a.index == "TEST" s...

ASP.MVC: Repository that reflects IQueryable but not Linq to SQL, DDD How To question

I want to create a DDD repository that returns IQueryable Entities that match the Linq to SQL underlying classes, minus any relations. I can easily return Entities minus the relations with a Linq select new {field, field, ... } projection. How do I code the Repository Entity class? How would I return an object from the repository havi...

LINQ's FirstOrDefault in SQL script?

Hello! What is the SQL language keyword for the LINQ-to-SQL FirstOrDefault or SingleOrDefault? Is it TOP(1)? EXAMPLE: SELECT TOP(1) @ItemCode = ItemCode FROM VendorItem WHERE VendorId = @VendorId There can't be more than 1 results anyway since there is a Unique Key constranint, do I have to spell out the TOP(1) or whatever it is? ...

How do I extend the Func delegate to contain more than the maximum four parameters?

I have been using LINQ with compiled queries, basically passing into the compiled query using Func but the problem is that it has a maximum of four parameters. Is it good practice to extend this? Is there any way to extend this or should I create my own delegate? Sometimes I need to pass six params and others five and others four or l...

LINQ-to-sql + C#. Only retrieve some columns from table to gridview

Hello. I try to pull some data and insert it into a gridview. But I must be doing something wrong since I can only select a single column. If I try to get both first and lastname then they will just be inserted into the same td in the gridview. Method so far is: public string[] ShowName() { LinqToEbuboo_20DataContext db = ...

LINQ : understanding and executing Groupby to bring back unique rows?

Hi there, i am trying to do a groupby in linq, basically i have a list ( along list - around 1000 entries) and i wish to groupby Description. The entries are vehicles, so hence there are 50 or so Ford Mondeos My query is pretty simple, no joins (yet :-) ) but it brings back a list including 50 Ford Mondeos, i wanted it to group them s...

Linq to SQL SP is returning multiple recordsets when I only want one!

Hi I have the following SP, however when I use LINQ to SQL it generates 2 multiple recordsets. For my sanity I am trying to fathom out what it is in the stored procedure that is doing this and would like to only return a single recordset... Can any help? ALTER PROCEDURE [dbo].[CheckToken] @LoginId int AS BEGIN ...

LINQ: Help with linq query and contains for an IEnumerable<string>?

Hi there, Can anyone help? I have a linq query which is embedded inside a extension method, it was working as v.RentalStatus was a String. I am now using a Group on my original query (the query is quite complex so i won't put it here). The importante thing is that v.RentalStatus = IEnumerable hence it can contain things like A (meani...

LINQ : saving files to a database

I want to save a pdf and mp3 file(s) to a SQL Server database and be able to retrieve from it. I'm still starting out with LINQ and don't master it yet. ...

"Only arguments that can be evaluated on the client are supported for the String.Contains method"

public static void MyFunction(MyErrorClass err) { var query = from filter in DataContext.ErrorFilters select filter; query = query.Where(f => err.ErrorMessage.Contains(f.ErrorMessage)); List<ErrorFilter> filters = query.ToList(); //...more code } So I'm having some issues with the above code, and I'm getting the error from the ...

LINQ to SQL: GroupBy() and Max() to get the object with latest date

Consider a SQL Server table that's used to store events for auditing. The need is to get only that latest entry for each CustID. We want to get the entire object/row. I am assuming that a GroupBy() will be needed in the query. Here's the query so far: var custsLastAccess = db.CustAccesses .Where(c.AccessReason.Leng...

Processing large datasets using LINQ

Every time I write a program of the form below using LINQ to SQL, I end up with a program that just grabs more and more memory as it runs and falls over in a heap consuming 2GB after perhaps as little as 25,000 records. I always end up rewriting it using ADO.NET. What am I doing wrong? Clarification: This question is not about speed ...

ASP.net: How to Test a LINQ Query

How can I test a LINQ Query such as the following: var vUser = (from u in this.dcLAUNCHOnline.aspnet_Users where u.UserName.Equals(this.wCreateUser.UserName) select u).Single(); I keep getting a null exception for vUser, however I am positive such a user exists. ...

Help with SQL/LINQ Debugging

I'm having trouble with the following statement, which is returning the error "Sequence contains no elements": var vUser = (from u in this.dcLAUNCHOnline.aspnet_Users where u.UserName.Equals(this.wCreateUser.UserName) select u).Single(); The SQL being generated: SELECT [t0].[A...

Error converting data type varchar to datetime when running raw SQL but not when called by LINQ to SQL

A client is getting an error when running my code. They sent me the SQL from profilder. When I paste it into SQL Server Management Studio it fails with: Error converting data type varchar to datetime However it doesn't fail when I run it on my local dev box or another clients production sever. To test I created a simple app with a...

What is the equivalent of LINQ-to-SQL's ExecuteCommand in Entity Framework?

I am porting an application running on LINQ-to-SQL to Entity Framework, and am having trouble finding an equivalent to ExecuteCommand: db.ExecuteCommand("INSERT Infos (Title) VALUES ('this is an added title')"); I found this site which tells me that it is possible to implement ExecuteCommand as an extension method on your ObjectContex...

Help with query: If was column copied, keep original out of select.

So I have this table that holds these 'assets', there are say 25 'special asset's that can not be edited by the users because they are shared. However as a way to allow the users to edit the asset and have their own version it makes a copy of the original that they are then allowed to edit. The row in the table holds a value called Origi...

How to determine why a distributed transaction is timing out

I am using LINQ to SQL and a third party SDK that supports distributed transactions. When I realize that a pending update will be updating both SQL records and records in the third party SDK, I am creating a TransactionScope with a 0 (presumably infinite) timeout (although I've also tried 12 hours as a timespan parameter). Then I use G...

LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator

Consider this LINQ To SQL query. It's intention is to take a string[] of search terms and apply the terms to a bunch of different fields on the SQL table: string[] searchTerms = new string[] {"hello","world","foo"}; List<Cust> = db.Custs.Where(c => searchTerms.Any(st => st.Equals(c.Email)) || searchTerms.Any(st => st.Equals(c.FirstN...

How to make only single database query when accessing Linq to SQL IEnumerable object?

My application constructs a Linq To Sql query and then populates a model which is then passed to the view. The view then accesses the IEnumerable object (from the Linq to Sql query) to display the user interface. The problem I am having is that each time the View accesses the IEnumerable object from the model, the linq to sql database ...