using System;
using System.Linq;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace DbCop
{
    public class SelectStarExpressionVisitor : TSqlFragmentVisitor
    {
        public override void ExplicitVisit(SelectStarExpression node)
        {
            Console.WriteLine(
                  "`Select *` detected @StartOffset:{0}, Line:{1}, T-SQL: {2}",
                  node.StartOffset,
                  node.StartLine,
                  string.Join(string.Empty, node.ScriptTokenStream.Select(x => x.Text)).Trim());

            base.ExplicitVisit(node);
        }
    }
}

    public static class GenericVisitor
    {
        public static void Start(string tSql, TSqlFragmentVisitor visitor)
        {
            IList<ParseError> errors;
            TSqlScript sqlFragment;
            using (var reader = new StringReader(tSql))
            {
                var parser = new TSql120Parser(initialQuotedIdentifiers: true);
                sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
            }

            if (errors != null && errors.Any())
            {
                var sb = new StringBuilder();
                foreach (var error in errors)
                    sb.AppendLine(error.Message);

                throw new InvalidOperationException(sb.ToString());
            }
            sqlFragment.Accept(visitor);
        }
    }

 var tsql = @"WITH ctex AS (
SELECT * FROM sys.objects
)
SELECT * FROM ctex";
GenericVisitor.Start(tsql, new SelectStarExpressionVisitor());

var productsList1 = ctx.Products.Where(product => product.Id > 1)
    .Include(product => product.Category)
    .Include(product => product.User)
    .Where(
        product =>
            product.Category.Title.Contains("t") && product.Category.Id > 1 && product.Price > 100)
    .OrderBy(product => product.Price)
    .ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[UserId] AS [UserId],
    [Extent3].[Id] AS [Id1],
    [Extent3].[Name] AS [Name1],
    [Extent3].[Title] AS [Title],
    [Extent3].[UserId] AS [UserId1],
    [Extent4].[Id] AS [Id2],
    [Extent4].[Name] AS [Name2]
    FROM    [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Categories] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Id]
    LEFT OUTER JOIN [dbo].[Users] AS [Extent4] ON [Extent1].[UserId] = [Extent4].[Id]
    WHERE ([Extent1].[Id] > 1) AND ([Extent2].[Title] LIKE N'%t%') AND ([Extent1].[CategoryId] > 1) AND ([Extent1].[Price] > 100)
    ORDER BY [Extent1].[Price] ASC

var query2 = from product in ctx.Products
             let category = product.Category
             where product.Id > 1
             where category.Title.Contains("t") && category.Id > 1 && product.Price > 100
             select new { product, category }; 
var productsList2 = query2.ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[UserId] AS [UserId],
    [Extent2].[Id] AS [Id1],
    [Extent2].[Name] AS [Name1],
    [Extent2].[Title] AS [Title],
    [Extent2].[UserId] AS [UserId1]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
    WHERE ([Extent1].[Id] > 1) AND ([Extent2].[Title] LIKE N'%t%') AND ([Extent2].[Id] > 1) AND ([Extent1].[Price] > 100)

var query2ChainedVersion = ctx.Products
     .Select(product => new { product, category = product.Category })
     .Where(@t => @t.product.Id > 1)
     .Where(@t => @t.category.Title.Contains("t") && @t.category.Id > 1 && @t.product.Price > 100)
     .Select(@t => new { @t.product , @t.category });

 SELECT Customers.CustomerID, Orders.OrderID
FROM Customers, Orders
WHERE  Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML RAW

 <row CustomerID="ALFKI" OrderID="10643" />
<row CustomerID="ALFKI" OrderID="10692" />

 SELECT Customers.CustomerID, Orders.OrderID
FROM Customers, Orders
WHERE  Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML RAW, ELEMENTS

 <row>
  <CustomerID>ALFKI</CustomerID>
  <OrderID>10643</OrderID>
</row>

 SELECT Customers.CustomerID,
Orders.OrderID
FROM Customers,
Orders
WHERE  Customers.CustomerID = Orders.CustomerID
ORDER BY
Customers.CustomerID
FOR XML RAW('Customer'), ELEMENTS XSINIL, ROOT('Customers'), XMLSCHEMA('http://MyCustomers')

 <Customers>
  <Customer xmlns="http://MyCustomers">
     <CustomerID>ALFKI</CustomerID>
     <OrderID>10643</OrderID>
  </Customer>

 SELECT Customers.CustomerID, Orders.OrderID
FROM Customers, Orders
WHERE  Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML AUTO, ELEMENTS

 <Customers>
  <CustomerID>ALFKI</CustomerID>
  <Orders>
     <OrderID>10643</OrderID>
  </Orders>
  <Orders>
     <OrderID>10692</OrderID>
  </Orders>
</Customers>

 SELECT 1 AS Tag,
NULL AS Parent,
Customers.CustomerID AS [Customers!1!CustomerID],
NULL AS [Order!2!OrderId]
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID
FROM Customers,
Orders
WHERE  Customers.CustomerID = Orders.CustomerID
ORDER BY
[Customers!1!CustomerID]
FOR XML EXPLICIT

 <Customers CustomerID="ALFKI">
  <Order OrderId="10643" />
  <Order OrderId="10692" />
  <Order OrderId="10702" />
  <Order OrderId="10835" />
  <Order OrderId="10952" />
  <Order OrderId="11011" />
</Customers>

 WITH XMLNAMESPACES('http://somens' AS au)
SELECT
  CustomerID AS [@au:CustomerID],
  CompanyName AS [Company/Name],
  ContactName AS [Contact/Name]  
FROM Customers
 FOR XML PATH('Customer')

 <Customer xmlns:au="http://somens" au:CustomerID="ALFKI">
  <Company>
       <Name>Alfreds Futterkiste</Name>
  </Company>
  <Contact>
      <Name>Maria Anders</Name>
  </Contact>
</Customer>

 FOR XML PATH('Customer'), Type

using System;

namespace EFCoreDbFunctionsSample.Entities
{
    public class Person
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public DateTime AddDate { get; set; }
    }
}

var usersInfo = context.People.Where(person => (DateTime.Now - person.AddDate).Days <= 10).ToList();

'The LINQ expression 'DbSet<Person>.Where(p => (DateTime.Now - p.AddDate).Days <= 10)'
could not be translated. Either rewrite the query in a form that can be translated,
or switch to client evaluation explicitly by inserting a call to either
AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

SELECT [p].[Id], [p].[AddDate], [p].[Name]
FROM [People] AS [p]
WHERE DATEDIFF(Day, [p].[AddDate], GETDATE()) <= 10

namespace EFCoreDbFunctionsSample.DataLayer
{
    public enum SqlDateDiff
    {
        Year,
        Quarter,
        Month,
        DayOfYear,
        Day,
        Week,
        Hour,
        Minute,
        Second,
        MilliSecond,
        MicroSecond,
        NanoSecond
    }

    public static class SqlDbFunctionsExtensions
    {
        public static int SqlDateDiff(SqlDateDiff interval, DateTime initial, DateTime end)
            => throw new InvalidOperationException($"{nameof(SqlDateDiff)} method cannot be called from the client side.");
        public static readonly MethodInfo SqlDateDiffMethodInfo = typeof(SqlDbFunctionsExtensions)
            .GetRuntimeMethod(
                nameof(SqlDbFunctionsExtensions.SqlDateDiff),
                new[] { typeof(SqlDateDiff), typeof(DateTime), typeof(DateTime) }
            );
    }
}

Library Written in C# For Parsing SQL Server T-SQL Scripts in .Net 

var post1Tags = context.BlogPosts
      .Where(blogPosts => blogPosts.Id == 1)
      .SelectMany(blogPosts => blogPosts.BlogPostsJoinTags)
      .Select(blogPostsJoinTags => blogPostsJoinTags.Tag)
      .Skip(0)
      .Take(10)
      .ToList();

SELECT   [blogPosts.BlogPostsJoinTags.Tag].[Id],
         [blogPosts.BlogPostsJoinTags.Tag].[Name]
FROM     [BlogPosts] AS [blogPosts]
         INNER JOIN
         [BlogPostsJoinTags] AS [blogPosts.BlogPostsJoinTags]
         ON [blogPosts].[Id] = [blogPosts.BlogPostsJoinTags].[BlogPostId]
         INNER JOIN
         [Tags] AS [blogPosts.BlogPostsJoinTags.Tag]
         ON [blogPosts.BlogPostsJoinTags].[TagId] = [blogPosts.BlogPostsJoinTags.Tag].[Id]
WHERE    [blogPosts].[Id] = 1
ORDER BY @@ROWCOUNT
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY;

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;