All Questions
33,058
questions
808
votes
32
answers
580k
views
Fastest Way of Inserting in Entity Framework
I'm looking for the fastest way of inserting in Entity Framework.
I'm asking this because of the scenario where you have an active TransactionScope and the insertion is huge (4000+). It can ...
- 9,530
482
votes
18
answers
688k
views
What is the syntax for an inner join in LINQ to SQL?
I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#.
How do you represent the following in LINQ to SQL:
select DealerContact.*
from ...
- 33.9k
387
votes
8
answers
495k
views
What represents a double in sql server?
I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float?
This will store ...
- 46.1k
366
votes
25
answers
388k
views
Entity Framework. Delete all rows in table
How can I quickly remove all rows in the table using Entity Framework?
I am currently using:
var rows = from o in dataDb.Table
select o;
foreach (var row in rows)
{
dataDb.Table.Remove(...
- 4,049
339
votes
17
answers
325k
views
Validation failed for one or more entities while saving changes to SQL Server Database using Entity Framework
I want to save my Edit to Database and I am using Entity FrameWork Code-First in ASP.NET MVC 3 / C# but I am getting errors. In my Event class, I have DateTime and TimeSpan datatypes but in my ...
- 4,023
301
votes
32
answers
330k
views
Generate class from database table
How can I generate a class from a SQL Server table object?
I'm not talking about using some ORM. I just need to create the entities (simple class). Something like:
public class Person
{
...
- 9,665
277
votes
10
answers
380k
views
How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5
I have a stored procedure that has three parameters and I've been trying to use the following to return the results:
context.Database.SqlQuery<myEntityType>("mySpName", param1, param2, param3);
...
- 5,174
274
votes
47
answers
79k
views
What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET ...
195
votes
28
answers
285k
views
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints
I make an outer join and executed successfully in the informix database but I get the following exception in my code:
DataTable dt = TeachingLoadDAL.GetCoursesWithEvalState(i, bat);
Failed to ...
- 11.3k
190
votes
18
answers
247k
views
How to get last inserted id?
I have this code:
string insertSql =
"INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId)";
using (SqlConnection myConnection = new SqlConnection(myConnectionString))
{
...
- 12.3k
170
votes
10
answers
224k
views
Distinct in Linq based on only one field of the table
I am trying to use .distinct in Linq to get result based on one field of the table (so do not require a whole duplicated records from table).
I know writing basic query using distinct as followed:
...
- 1,857
169
votes
21
answers
8k
views
Joins are for lazy people?
I recently had a discussion with another developer who claimed to me that JOINs (SQL) are useless. This is technically true but he added that using joins is less efficient than making several requests ...
- 18.1k
168
votes
12
answers
355k
views
How to execute an .SQL script file using c#
I'm sure this question has been answered already, however I was unable to find an answer using the search tool.
Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, ...
- 3,732
160
votes
7
answers
167k
views
LINQ to SQL - Left Outer Join with multiple join conditions
I have the following SQL, which I am trying to translate to LINQ:
SELECT f.value
FROM period as p
LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17
WHERE p.companyid = 100
I have ...
- 5,754
155
votes
8
answers
365k
views
Execute Insert command and return inserted Id in Sql
I am inserting some values into a SQL table using C# in MVC 4. Actually, I want to insert values and return the 'ID' of last inserted record. I use the following code.
public class MemberBasicData
{
...
- 5,283
151
votes
16
answers
414k
views
DateTime format to SQL format using C#
I am trying to save the current date time format from C# and convert it to an SQL Server date format like so yyyy-MM-dd HH:mm:ss so I can use it for my UPDATE query.
This was my first code:
DateTime ...
- 2,905
140
votes
11
answers
149k
views
Could not find an implementation of the query pattern
In my silverlight application I am trying to create a database connection using LINQ.
First I add a new LINQ to SQL class, and drag my table called "tblPersoon" into it.
Then in my service file I try ...
- 2,772
138
votes
13
answers
239k
views
LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression
I'm migrating some stuff from one mysql server to a sql server but i can't figure out how to make this code work:
using (var context = new Context())
{
...
foreach (var item in collection)
...
- 15.5k
137
votes
6
answers
221k
views
Update multiple rows in Entity Framework from a list of ids
I am trying to create a query for Entity Framework that will allow me to take a list of ids and update a field associated with them.
Example in SQL:
UPDATE Friends
SET msgSentBy = '1234'
WHERE id IN (...
- 7,175
134
votes
14
answers
245k
views
Data is Null. This method or property cannot be called on Null values
I'm working on an application where one can get information on movies from a database as well as add, update and delete the movies. In the database I have three tables (Movie, Genre and MovieGenre <...
- 20.4k
132
votes
7
answers
308k
views
Index (zero based) must be greater than or equal to zero
Hey I keep getting an error:
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
My code:
OdbcCommand cmd = new OdbcCommand("SELECT FirstName, ...
- 6,050
130
votes
8
answers
231k
views
C# SQL Server - Passing a list to a stored procedure
I am calling a SQL Server stored procedure from my C# code:
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", ...
- 11.8k
126
votes
10
answers
190k
views
How to get max value of a column using Entity Framework?
To get maximum value of a column that contains integer, I can use the following T-SQL comand
SELECT MAX(expression )
FROM tables
WHERE predicates;
Is it possible to obtain the same result with ...
- 20.9k
125
votes
6
answers
208k
views
Checking for empty or null JToken in a JObject
I have the following...
JArray clients = (JArray)clientsParsed["objects"];
foreach (JObject item in clients.Children())
{
// etc.. SQL params stuff...
command.Parameters["@MyParameter"]....
- 5,467
121
votes
12
answers
228k
views
ALTER TABLE DROP COLUMN failed because one or more objects access this column
I am trying to do this:
ALTER TABLE CompanyTransactions DROP COLUMN Created
But I get this:
Msg 5074, Level 16, State 1, Line 2
The object 'DF__CompanyTr__Creat__0CDAE408' is dependent on ...
- 13.7k
120
votes
6
answers
503k
views
How to read SQL Table data into a C# DataTable
I've read a lot of posts about inserting a DataTable into a SQL table, but how can I pull a SQL table into a C#/.NET DataTable?
- 1,337
119
votes
24
answers
67k
views
Is there a better way to dynamically build an SQL WHERE clause than by using 1=1 at its beginning?
I'm building some SQL query in C#. It will differ depending on some conditions stored as variables in the code.
string Query="SELECT * FROM Table1 WHERE 1=1 ";
if (condition1)
Query += "AND Col1=...
- 3,441
115
votes
15
answers
116k
views
Is there a C# IN operator?
In SQL, you can use the following syntax:
SELECT *
FROM MY_TABLE
WHERE VALUE_1 IN (1, 2, 3)
Is there an equivalent in C#? The IDE seems to recognise "in" as a keyword, but I don't seem to be able ...
- 16.5k
114
votes
11
answers
16k
views
Is it safe to not parameterize an SQL query when the parameter is not a string?
In terms of SQL injection, I completely understand the necessity to parameterize a string parameter; that's one of the oldest tricks in the book. But when can it be justified to not parameterize an ...
- 7,410
114
votes
8
answers
151k
views
How to get Database Name from Connection String using SqlConnectionStringBuilder
I do not want to split connection strings using string manipulation functions to get Server, Database, Username, and Password.
I read the following link and read the accepted answer, I found that is ...
- 7,419
110
votes
3
answers
42k
views
What is the difference between "LINQ to Entities", "LINQ to SQL" and "LINQ to Dataset"
I've been working for quite a while now with LINQ. However, it remains a bit of a mystery what the real differences are between the mentioned flavours of LINQ.
The successful answer will contain a ...
- 15.5k
108
votes
15
answers
243k
views
Error when connect database continuously
When I am querying from database in continuous looping, after some time
I get an error :
An exception has been raised that is likely due to a transient
failure. If you are connecting to a SQL ...
- 1,081
107
votes
3
answers
201k
views
how to update the multiple rows at a time using linq to sql?
Table:
id userid friendid name status
1 1 2 venkat false
2 1 3 sai true
3 1 4 arun false
4 1 5 arjun ...
- 1,863
103
votes
4
answers
95k
views
Is it necessary to manually close and dispose of SqlDataReader?
I'm working with legacy code here and there are many instances of SqlDataReader that are never closed or disposed. The connection is closed but, I am not sure if it is necessary to manage the reader ...
- 1,428
96
votes
7
answers
468k
views
How do I extract data from a DataTable?
I have a DataTable that is filled in from an SQL query to a local database, but I don't know how to extract data from it.
Main method (in test program):
static void Main(string[] args)
{
const ...
- 39k
92
votes
8
answers
90k
views
How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
According this thread, we can log the generated SQL via EF, but what about DbContext.SaveChanges()? Is there any easy way to do this job without any extra frameworks?
- 8,187
92
votes
6
answers
83k
views
How to escape simple SQL queries in C# for SqlServer
I use an API that expects a SQL string. I take a user input, escape it and pass it along to the API. The user input is quite simple. It asks for column values. Like so:
string name = userInput.Value;...
- 1,936
82
votes
9
answers
478k
views
Connecting to SQL Server using windows authentication
When I was trying to connect to SQL Server using the following code:
SqlConnection con = new SqlConnection("Server=localhost,Authentication=Windows Authentication, Database=employeedetails");
con....
- 919
81
votes
8
answers
51k
views
Why does the Contains() operator degrade Entity Framework's performance so dramatically?
UPDATE 3: According to this announcement, this has been addressed by the EF team in EF6 alpha 2.
UPDATE 2: I've created a suggestion to fix this problem. To vote for it, go here.
Consider a SQL ...
- 7,633
80
votes
5
answers
280k
views
Increasing the Command Timeout for SQL command
I have a little problem and hoping someone can give me some advice. I am running a SQL command, but it appears it takes this command about 2 mins to return the data as there is a lot of data. But the ...
- 1,003
80
votes
5
answers
167k
views
C# guid and SQL uniqueidentifier
I want to create a GUID and store it in the DB.
In C# a guid can be created using Guid.NewGuid(). This creates a 128 bit integer. SQL Server has a uniqueidentifier column which holds a huge ...
- 16.1k
80
votes
8
answers
114k
views
Parsing SQL code in C# [closed]
I want to parse SQL code using C#.
Specifically, is there any freely available parser which can parse SQL code and generate a tree or any other structure out of it? It should also generate the proper ...
- 2,594
78
votes
16
answers
347k
views
Getting return value from stored procedure in C#
I have the following query:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) output
AS
SET @Password =
(SELECT Password
FROM dbo....
- 66.3k
78
votes
3
answers
100k
views
Passing Output parameters to stored procedure using dapper in c# code
I have a stored procedure in this format
CREATE PROCEDURE SP_MYTESTpROC
@VAR1 VARCHAR(10),
@VAR2 VARCHAR(20),
@BASEID INT ,
@NEWID INT OUTPUT
As Begin
INSERT INTO TABLE_NAME(...
- 1,293
77
votes
14
answers
164k
views
Entity Framework error: Cannot insert explicit value for identity column in table
I'm getting this error on EF.
Cannot insert explicit value for identity column in table
'GroupMembers_New' when IDENTITY_INSERT is set to OFF.
The column on the Db is identity increment and on ...
- 3,522
75
votes
13
answers
312k
views
Sql connection-string for localhost server
I am newbie in this .NET and please don't mind in answering my simple question.
I am trying to write a windows application, where in I am using a localhost SQLserver for database.
I need to know what ...
- 977
75
votes
8
answers
170k
views
How to pass a null variable to a SQL Stored Procedure from C#.net code
Im calling a SQL stored procedure from a piece of C#.net code:
SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters);
where the sqlParameters variable ...
- 2,489
75
votes
5
answers
83k
views
Convert SQL to Linq left join with null
How can I convert properly this SQL to linq
select t1.ProgramID
from Program t1 LEFT JOIN ProgramLocation t2 ON t1.ProgramID = t2.ProgramID
where t2.ProgramID IS NULL
I try that but it not ...
- 13.6k
73
votes
13
answers
70k
views
Entity Framework The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
On updating database in Entity Framework , Code first Migration, I am getting this error:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Clients_dbo....
- 1,672
73
votes
5
answers
37k
views
Is there a good LINQ way to do a cartesian product?
I have a class structure like so:
Person
Dogs (dog 1, dog 2, etc)
Puppies (puppy A, puppy B, etc)
There is one person. He has 1..n dogs. Each dog has 1..n puppies.
I want a list of all the possible ...
- 40.2k