var userName = "user 1";
var user = context.Users.FromSql($"select top 1 * from Users where name = {userName} ").FirstOrDefault();
if (user != null)
{
   Console.WriteLine(user.Name);
}

SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name]
FROM (
    select top 1 * from Users where name = @p0
) AS [u]

var sql = $"select top 1 * from Users where name = {userName} ";
user = context.Users.FromSql(sql).FirstOrDefault();
if (user != null)
{
   Console.WriteLine(user.Name);
}

 .SqlException: Incorrect syntax near '1'

 public static IQueryable<TEntity> FromSql<TEntity>(this IQueryable<TEntity> source, FormattableString sql) where TEntity : class;

 var sql = $"select top 1 * from Users where name = '{userName}' ";

SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name]
FROM (
   select top 1 * from Users where name = 'user 1'
) AS [u]

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();

var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogs")
  .ToList();

var user = "johndoe";

var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
  .ToList();

var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
  .ToList();

 var results = _context.Contacts.FromSql(
@"SELECT Id, Name Address, City, State, Zip 
    FROM Contacts 
    WHERE Name IN (@p0, @p1)", name1, name2);

var nameParameter = new SqlParameter
{
  ParameterName = "@name",
  Value = "doc",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.NVarChar
};

var posts = context.Posts
  .FromSql("SELECT * FROM dbo.GetMatchingPostByTitle({0})", searchTerm)
  .Where(p => p.BlogId == 1)
  .OrderByDescending(p => p.CreateDate)
  .ToList();

var searchTerm = ".NET";
var blogs = context.Blogs
  .FromSql("SELECT * FROM dbo.SearchBlogs {0}", searchTerm)
  .Include(b => b.Posts)
  .ToList();

  context.Database.ExecuteSqlCommand("UPDATE dbo.People SET FirstName = 'Jane' WHERE PersonId = 30");

context.Database.ExecuteSqlCommand("usp_CreateShipper @p0, @p1",
  parameters: new[] { "hello", "world" });

using (var connection = context.Database.GetDbConnection())
{
    connection.Open();
 
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM Contacts";
        var result = command.ExecuteScalar().ToString();
    }
}

using System;

namespace EFCoreSoftDelete.Entities
{
    public abstract class BaseEntity
    {
        public int Id { get; set; }


        public bool IsDeleted { set; get; }
        public DateTime? DeletedAt { set; get; }
    }
}

using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace EFCoreSoftDelete.Entities
{
    public class Blog : BaseEntity
    {
        public string Name { set; get; }

        public virtual ICollection<Post> Posts { set; get; }
    }

    public class BlogConfiguration : IEntityTypeConfiguration<Blog>
    {
        public void Configure(EntityTypeBuilder<Blog> builder)
        {
            builder.Property(blog => blog.Name).HasMaxLength(450).IsRequired();
            builder.HasIndex(blog => blog.Name).IsUnique();

            builder.HasData(new Blog { Id = 1, Name = "Blog 1" });
            builder.HasData(new Blog { Id = 2, Name = "Blog 2" });
            builder.HasData(new Blog { Id = 3, Name = "Blog 3" });
        }
    }
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace EFCoreSoftDelete.Entities
{
    public class Post : BaseEntity
    {
        public string Title { set; get; }

        public Blog Blog { set; get; }
        public int BlogId { set; get; }
    }

    public class PostConfiguration : IEntityTypeConfiguration<Post>
    {
        public void Configure(EntityTypeBuilder<Post> builder)
        {
            builder.Property(post => post.Title).HasMaxLength(450);
            builder.HasOne(post => post.Blog).WithMany(blog => blog.Posts).HasForeignKey(post => post.BlogId);

            builder.HasData(new Post { Id = 1, BlogId = 1, Title = "Post 1" });
            builder.HasData(new Post { Id = 2, BlogId = 1, Title = "Post 2" });
            builder.HasData(new Post { Id = 3, BlogId = 1, Title = "Post 3" });
            builder.HasData(new Post { Id = 4, BlogId = 1, Title = "Post 4" });
            builder.HasData(new Post { Id = 5, BlogId = 2, Title = "Post 5" });
        }
    }
}

    public class BlogConfiguration : IEntityTypeConfiguration<Blog>
    {
        public void Configure(EntityTypeBuilder<Blog> builder)
        {
            // ...
            builder.HasQueryFilter(blog => !blog.IsDeleted);
        }
    }

SELECT [b].[Id], [b].[DeletedAt], [b].[IsDeleted], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[IsDeleted] <> CAST(1 AS bit)

namespace EFCoreSoftDelete.DataLayer
{
    public static class GlobalFiltersManager
    {
        public static void ApplySoftDeleteQueryFilters(this ModelBuilder modelBuilder)
        {
            foreach (var entityType in modelBuilder.Model
                                                    .GetEntityTypes()
                                                    .Where(eType => typeof(BaseEntity).IsAssignableFrom(eType.ClrType)))
            {
                entityType.addSoftDeleteQueryFilter();
            }
        }

        private static void addSoftDeleteQueryFilter(this IMutableEntityType entityData)
        {
            var methodToCall = typeof(GlobalFiltersManager)
                                .GetMethod(nameof(getSoftDeleteFilter), BindingFlags.NonPublic | BindingFlags.Static)
                                .MakeGenericMethod(entityData.ClrType);
            var filter = methodToCall.Invoke(null, new object[] { });
            entityData.SetQueryFilter((LambdaExpression)filter);
        }

        private static LambdaExpression getSoftDeleteFilter<TEntity>() where TEntity : BaseEntity
        {
            return (Expression<Func<TEntity, bool>>)(entity => !entity.IsDeleted);
        }
    }
}

namespace EFCoreSoftDelete.DataLayer
{
    public class ApplicationDbContext : DbContext
    {

        //...


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.ApplyConfigurationsFromAssembly(typeof(BaseEntity).Assembly);
            modelBuilder.ApplySoftDeleteQueryFilters();
        }

namespace EFCoreSoftDelete.DataLayer
{
    public static class AuditableEntitiesManager
    {
        public static void SetAuditableEntityOnBeforeSaveChanges(this ApplicationDbContext context)
        {
            var now = DateTime.UtcNow;

            foreach (var entry in context.ChangeTracker.Entries<BaseEntity>())
            {
                switch (entry.State)
                {
                    case EntityState.Added:
                        //TODO: ...
                        break;
                    case EntityState.Modified:
                        //TODO: ...
                        break;
                    case EntityState.Deleted:
                        entry.State = EntityState.Unchanged; //NOTE: For soft-deletes to work with the original `Remove` method.

                        entry.Entity.IsDeleted = true;
                        entry.Entity.DeletedAt = now;
                        break;
                }
            }
        }
    }
}

var post1 = context.Posts.Find(1);
if (post1 != null)
{
   context.Remove(post1);

   context.SaveChanges();
}

Executing DbCommand [Parameters=[@p2='1', @p0='2020-09-17T05:11:32' (Nullable = true), @p1='True'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Posts] SET [DeletedAt] = @p0, [IsDeleted] = @p1
WHERE [Id] = @p2;
SELECT @@ROWCOUNT;

namespace EFCoreSoftDelete.DataLayer
{
    public class ApplicationDbContext : DbContext
    {
        // ...

        public override int SaveChanges(bool acceptAllChangesOnSuccess)
        {
            ChangeTracker.DetectChanges();

            beforeSaveTriggers();

            ChangeTracker.AutoDetectChangesEnabled = false; // for performance reasons, to avoid calling DetectChanges() again.
            var result = base.SaveChanges(acceptAllChangesOnSuccess);

            ChangeTracker.AutoDetectChangesEnabled = true;
            return result;
        }

        // ...

        private void beforeSaveTriggers()
        {
            setAuditProperties();
        }

        private void setAuditProperties()
        {
            this.SetAuditableEntityOnBeforeSaveChanges();
        }
    }
}

 ar blog1 = context.Blogs.FirstOrDefault(blog => blog.Id == 1);
if (blog1 != null)
{
   context.Remove(blog1);

   context.SaveChanges();
}

var blog1AndItsRelatedPosts = context.Blogs
    .Include(blog => blog.Posts)
    .FirstOrDefault(blog => blog.Id == 1);
if (blog1AndItsRelatedPosts != null)
{
    context.Remove(blog1AndItsRelatedPosts);

    context.SaveChanges();
}

SELECT [t].[Id], [t].[DeletedAt], [t].[IsDeleted], [t].[Name], [t0].[Id], [t0].[BlogId], [t0].[DeletedAt], [t0].[IsDeleted], [t0].[Title]
FROM (
SELECT TOP(1) [b].[Id], [b].[DeletedAt], [b].[IsDeleted], [b].[Name]
FROM [Blogs] AS [b]
WHERE ([b].[IsDeleted] <> CAST(1 AS bit)) AND ([b].[Id] = 1)
) AS [t]
LEFT JOIN (
SELECT [p].[Id], [p].[BlogId], [p].[DeletedAt], [p].[IsDeleted], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[IsDeleted] <> CAST(1 AS bit)
) AS [t0] ON [t].[Id] = [t0].[BlogId]
ORDER BY [t].[Id], [t0].[Id]

Executing DbCommand [Parameters=[@p2='1', @p0='2020-09-17T05:25:00' (Nullable = true), @p1='True',
 @p5='2', @p3='2020-09-17T05:25:00' (Nullable = true), @p4='True', @p8='3',
@p6='2020-09-17T05:25:00' (Nullable = true), @p7='True',
 @p11='4', @p9='2020-09-17T05:25:00' (Nullable = true), @p10='True'], CommandType='Text', CommandTimeout='30']

SET NOCOUNT ON;
UPDATE [Blogs] SET [DeletedAt] = @p0, [IsDeleted] = @p1
WHERE [Id] = @p2;
SELECT @@ROWCOUNT;

UPDATE [Posts] SET [DeletedAt] = @p3, [IsDeleted] = @p4
WHERE [Id] = @p5;
SELECT @@ROWCOUNT;

UPDATE [Posts] SET [DeletedAt] = @p6, [IsDeleted] = @p7
WHERE [Id] = @p8;
SELECT @@ROWCOUNT;

UPDATE [Posts] SET [DeletedAt] = @p9, [IsDeleted] = @p10
WHERE [Id] = @p11;
SELECT @@ROWCOUNT;

 SELECT * FROM table1 WHERE OrderDate ='12 Mar 2004'

SET @SQL = 'SELECT * FROM table2 WHERE OrderDate = ' + '''' + @Var + ''''
EXEC (@SQL)

  --Forced
ALTER DATABASE dbName SET PARAMETERIZATION FORCED

--Simple
ALTER DATABASE dbName SET PARAMETERIZATION SIMPLE

 use dbName;
-- Optimizing for Ad hoc Workloads
exec sp_configure 'show advanced options',1;
RECONFIGURE;
go
exec sp_configure 'optimize for ad hoc workloads',1;
RECONFIGURE;
Go

SqlCommand cmd=new SqlCommand ("select count(*) from login where user='"+userName+"' and pass='"+password+"'",con);

select count(*) from login where user='admin' and pass='salam123'

' or 1=1 --

select count(*) from login where user='admin' and pass='' or 1=1 --'

SqlCommand cmd=new SqlCommand ("select count(*) from login where user=@u and pass=@p",con);

cmd.Parameters.Add("@u", SqlDbType.Varchar, 10).Value=TextLogin.Text.Trim();

cmd.Parameters.Add("@p", SqlDbType.Varchar,10).Value=TextPwd.Text.Trim();