Questions tagged [entity-framework]
For questions relating to Microsoft's object-relational mapper.
111 questions
0
votes
1
answer
107
views
Why is this PostgreSQL query with LIMIT so slow?
I have a .NET WebApi with two entities (please forgive the silly names, it's just an example):
public class Father
{
public int Id { get; set; }
public string Name { get; set; } = null!;
...
- 103
1
vote
1
answer
447
views
Execution timeout exception in ASP.NET core
My problem is a quite hard to define. We have an SQL Server (version 15.0.2104.1) stored procedure that fills out our web site home page with data. When executed from SSMS it takes abount 0.5 second ...
- 25
0
votes
0
answers
278
views
Insert data into Always Encrypted column using stored procedure in MSSQL
I am facing an issue while trying to insert data into SQL table which is having encrypted columns in it.
Following is my approach.
I have created a table with three columns: [ID], [Name], [Password].
[...
- 11
0
votes
2
answers
609
views
Is high logical read terrible for performance? AFAIK this is reading from memory, which is fast
I have database query that seems to have abnormally high logical read (refer below).
I am not a DBA, but from the plan it seems pretty optimized to me? There are 2 index scans but the cost isn't that ...
- 101
1
vote
1
answer
3k
views
No connection possible after TrustServerCertificate=True => The certificate chain was issued by an authority that is not trusted
I have a machine which could connect to an MS SQL Server in the past. I have SSMS installed and the connection was possible when I activated TrustServerCertificate checkbox in SSMS. I could connect ...
0
votes
1
answer
200
views
EF Queries Using Read Commited Though snapshot_isolation_state and is_read_committed_snapshot_on are ON
I am running this query,
SELECT name
, snapshot_isolation_state
, is_read_committed_snapshot_on
FROM sys.databases s
name snapshot_isolation_state is_read_committed_snapshot_on
master ...
0
votes
1
answer
111
views
Maintain changes in database via generic approach triggers into history table
Our main system database has temporal tables
These have helped us hugely in working out issues
However. A drawback of this is that an issue with doing a lot of updates at once can lead to a an error ...
3
votes
1
answer
1k
views
Debugging slow SQL Server performance with SQL Profiler
I have been debugging an issue in our production environment(on-premises) where queries perform slowly even the columns are indexed. The table has roughly 200 million rows, is not partitioned and is ...
- 33
2
votes
1
answer
185
views
Possible overhead of adding columns in a table without index in SQL Server
My question somehow relates to this one Performance considerations when adding a new column to a big table in Sql Server, but i still think there are a couple of differences so i'll ask anyways.
We've ...
- 121
0
votes
2
answers
569
views
What if we do not use MAX function
I was supposed to use a script to get the MAX value from a table. It should have looked like this
BEGIN
DECLARE @MaxID AS INT
SELECT @MaxID = MAX([Id]) FROM dbo.suggestion
Insert into suggestion
...
- 223
4
votes
1
answer
4k
views
HOLDLOCK required in MERGE with UPDATE
Within a .NET 6 application the EntityFramework is used to keep track of entities and to update related fields. To improve bulk updates, the EfCore.BulkExtensions package has been added.
During a ....
- 153
-1
votes
1
answer
450
views
MS SQL Server Express alternatives, choosing between Relational-DB / NoSQL [closed]
This question relates to choosing the best database storing data on a pc running windows.
The pc is part of a commercial product which generates a big amount of data over time.
Data consists of
Test ...
- 177
2
votes
3
answers
619
views
Database Design - Relationship between two or more junctions tables
I am a junior developer and I don't know how to implement this next relationship.
Note: All entities described here are fictional but have equivalents in an actual production Database. Performance is ...
- 123
0
votes
1
answer
480
views
Can we delete data using SQL job and insert data into the same table at the same time from another application
I have created one SQL job and it is executing a stored procedure to delete data from the table at a specific time.
But at the same time, if anyone will try to insert a record in that same table from ...
- 111
2
votes
2
answers
710
views
What is most efficient design pattern for a sql database containing billions of rows per user in a single table?
I work on a relatively large system where have started to run into performance problems scaling for multiple users.
The system is a .NET application, so query's are written using an ORM (entity ...
- 23
1
vote
1
answer
749
views
Adding soft delete to a database after having used hard delete
Introduction
My app collects data from a centralized source where many different users can submit data about their organisation and their staff.
Previously we used to just hard delete a users data ...
- 21
0
votes
2
answers
229
views
User handling in MS-SQL database
I have an MS-SQL installation, controlling different databases. This can be accessed using Microsoft SQL Server Management Studio 18. For doing that, I use Windows authentication.
In there, I have ...
- 377
11
votes
3
answers
2k
views
MERGE into a view with INSTEAD OF triggers
I have a view with instead of triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of merge statement. Here's my table and view:
create table tbl (id ...
- 213
0
votes
1
answer
262
views
Right way to store several addresses and select one as main/principal
I'm working in a little model and I'm struggling with a simple question.
How should I store a list of addresses/mails/phones and select one as principal?
Example I'm thinking now.
AddressEntity:
Id
...
- 103
2
votes
1
answer
6k
views
There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)
Simplified code
var sql = @" create table #Temp(
int Id NOT NULL
CONSTRAINT [PK_TempId] PRIMARY KEY (Id)
)
insert into #temp(id) select (...
- 195
3
votes
2
answers
1k
views
SQL Server - Automatically full backup for new databases which are auto-created with EntityFramework-Migrations
I have an ASP.NET MVC Application with a Multi-Tenant architecture (using SQL-Server). I'm using EntityFramework DB-Migrations which automatically generates a database per customer.
In SQL-Server I ...
- 133
0
votes
1
answer
533
views
Is there a way to tell if a GUID column contains sequential or traditional / random GUID's?
Have a table that is populated by Entity Framework 3.1.1 code. The table contains a clustered index / primary key on ID which is defined as of type GUID with no default value. Is there a way in SQL ...
- 1,127
0
votes
2
answers
737
views
Update via C# EF Core or SQL
Our work involves updating products, we have a large table of products, and the prices and other pertinent information gets updated hourly. Let’s say it’s an amazon store, and we are talking of amazon ...
1
vote
1
answer
288
views
Multiple roundtrips vs cross joins
I have a feeling the answer is "it depends on...", but I'm wondering if there's a concrete answer.
In Entity Framework, you build a query using C# code, and the framework converts to SQL and ...
- 137
2
votes
1
answer
1k
views
Troubleshooting spid suspended ASYNC_NETWORK_IO
Recently we moved our database to a new server
The old server was running
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08
Copyright (C) 2017 Microsoft ...
- 195
-1
votes
1
answer
2k
views
Use the following business rules to create a Crow’s Foot ERD
Is there any mistake in the ERD???? it looks weird.
- 1
0
votes
0
answers
231
views
How to compare EF core code-first database with existing Azure Sql Server database
we have multi-tenants databases and we have special stored procedure to compare the structure of the tenents after each release. Now, we are moving to Azure SQL Server and the stored procedure is not ...
5
votes
1
answer
2k
views
SQL Server - Very high disk activity after running query?
I'm running SQL Server Express 12.0.4100 on an AWS instance with 16 cores and 64 GB of RAM with a 3TB and 9000 IOPS EBS attached. This has been running for 2 years perfectly with no issues until this ...
- 183
0
votes
1
answer
2k
views
MSSQL Join between big and empty tables
I've got an issue with estimated cost and join predicate :
SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500
OPTION (RECOMPILE)
...
- 3
1
vote
1
answer
204
views
Entity Relationship Diagram - Entity depending on attribute
I have an ER-Diagram containing the entity employees. When employees have high skills they may supervise lower skilled employees. How can I model that in an ER-Diagram?
My initial approach of was to ...
- 39
-2
votes
2
answers
174
views
INSTEAD OF INSERT does not display changes
This is my table:
CREATE TABLE [MyTable]
(
[GroupId] INT NOT NULL,
[ItemId] INT NOT NULL,
[Data] INT NOT NULL,
PRIMARY KEY CLUSTERED ([GroupId], [...
- 97
0
votes
1
answer
4k
views
Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file [closed]
Example in the EF6 migration file Up method.
I wish to add a new column B in existing table Country.
Sql("ALTER TABLE [dbo].[Country]
ADD [B] INT DEFAULT((0)) NOT NULL;")
Then I will need to ...
- 103
0
votes
0
answers
539
views
Database design for multilingual tags
I'm looking for a simple database design for tags, where the tag-label can be translated (multilingual).
I'm currently looking at Toxi, which is a very simple and clean way of creating a tagging ...
- 309
2
votes
0
answers
176
views
Code First Migrations with an existing database - How do I make sure migration won't affect the existing data?
I am referring https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/existing-database
I am using Entity frame work Code First Migrations for an existing database.
I get the below ...
- 269
0
votes
2
answers
2k
views
INNER JOIN is extremly slow on some rows and create temp table
The MySQL query
SELECT `p`.*,
`p.Author`.*
FROM `post` AS `p`
INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid`
WHERE ( ( `p`.`threadid` = 29780 )
AND ( `...
- 151
3
votes
2
answers
488
views
SQL Server one-to-many and index fragmentation
I'm currently writing code to update an entities child collection (one-to-many relationship), and while thinking about how to write code to determine which entities have been added/removed/modified, I ...
- 83
3
votes
2
answers
696
views
Performance problem with big query on first run
I'm having a problem with a query usually being so slow on first time run that it times out and then executing in under a second once it's loaded into cache.
The query looks something like this
exec ...
- 133
3
votes
1
answer
3k
views
Can Entity Framework access In Memory OLTP Tables?
Are there any restrictions with having Entity Framework access In Memory OLTP Tables? Or is this not possible?
user158823
3
votes
1
answer
230
views
Delete Joined to Derived Table Causes Index Scan and Table Spool
Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates.
...
- 1,179
3
votes
1
answer
8k
views
Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)
I have TBV function . Like this
ALTER FUNCTION [dbo].[fn_Functiont]
(
@accessibleIds ListAccesableIds READONLY
)
RETURNS TABLE
AS
RETURN
(
SELECT d.*, b.Name AS Name, ps.Name AS ...
- 145
2
votes
0
answers
1k
views
Keeping MySql connections alive
I'm running a Windows service, which connects to a MySql database via Entity Framework.
Every 10 seconds it issues a query to the database to see if any new records have been added. If it finds any, ...
- 243
5
votes
1
answer
471
views
Empty result takes longer with all columns included
I'm executing a query every 1 second and most of the time it returns no results (SQL generated by Entity Framework):
SELECT TOP (5)
[Extent1].[ID] AS [ID],
***30 more columns***
FROM ...
- 153
1
vote
1
answer
378
views
Inserts via Entity Framework on server take about double the time than locally
I'm inserting lots of data into SQL Server with many of them having foreign key dependencies. Meaning if I insert 1 row in my Person table, I'm also inserting 1 row in 8 other related tables.
This ...
- 11
1
vote
2
answers
3k
views
SQL Server Order SELECT List with condition
I want order the select list with condition. For example; I have t1 table and it has 3 columns and 6 rows
Table t1:
+----+---+---+
| id | x | y |
+----+---+---+
| 1 | a | 1 |
| 2 | b | 3 |
| 3 |...
- 15
0
votes
1
answer
510
views
My first database schema design
I'm new to programming and databases and working on my first application using .Net, Entity (for SQL Server) and Identity.
My application will allow users to create html pages for online courses. ...
- 103
1
vote
1
answer
1k
views
sp_executesql processes uniqueidentifier-param with EntityCommandExecutionException but still acts like expected
I use a Azure DB(MS-Sql) and administrate the DB with Management Studio for SQL Server 2016. My Application is a simple C#/WPF-Application.
So here we go, this is my SProc:
ALTER PROCEDURE [dbo]....
- 13
2
votes
1
answer
200
views
Entity Framework Coupling with Database
I am new to EF and Database Design. I have been studying about using Repository Pattern and Unit of Work a lot, and I have seen a lot of queries made with LINQ.
But I have one question. If, in ...
3
votes
3
answers
4k
views
Visual Studio database project and Enity Framework code-first
At work we are developing our latest module in C# Entity framework code-first approach. I have been given the role of developing databases from the DBContexts from within the project/s.
I generated ...
- 131
2
votes
1
answer
224
views
DB Design Challenge
I don't have much experience designing databases and I have this case that I stumbled upon where I hope there is a definite answer in terms of DB design methodologies. I also have to mention that I am ...
- 245
-1
votes
2
answers
485
views
Custom Paging in Stored Procedure (Optimizing the Performance)
I have a stored procedure that is working perfectly but the issue is performance. There are so many joins in this stored procedure and unions... So after having the query executed, i need to send only ...
