create table t1
(
col1 char(1),
col2 char(1)
)

create unique nonclustered index ix_uq on t1 (col1 , col2);

insert t1 
values('A', 'B'), ('B', 'C'), ('C', 'D'), ('D', 'E'), ('E', 'F'),
('G', 'H'), ('I', 'J'), ('K', 'L'), ('M', 'N'), ('O', 'P');

select col1 + col2
from t1
where col1 + col2 = 'OP'

select col1 + col2
from t1
where col1  = 'O' and col2 = 'P'

alter table t1 add col3 as col1 + col2 persisted

create clustered index ix1 on t1 (col3)

select *
from t1
where col3 = 'OP';

• Custom Membership Providers 
• Codefirstmembership
• EFmembership
• ...

using System.Data.Sql;

class Program
{
  static void Main()
  {
    // Retrieve the enumerator instance and then the data.
    SqlDataSourceEnumerator instance =
      SqlDataSourceEnumerator.Instance;
    System.Data.DataTable table = instance.GetDataSources();

    // Display the contents of the table.
    DisplayData(table);

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
  }

  private static void DisplayData(System.Data.DataTable table)
  {
    foreach (System.Data.DataRow row in table.Rows)
    {
      foreach (System.Data.DataColumn col in table.Columns)
      {
        Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
      }
      Console.WriteLine("============================");
    }
  }
}

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

            var key = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names");

            foreach (string sk in key.GetSubKeyNames())
            {
                var rkey = key.OpenSubKey(sk);
                foreach (string s in rkey.GetValueNames())
                {
                    MessageBox.Show("Sql instance name:" + s);
                }
            }

 Trace.WriteLine(string.Format("Changed Entity Names: {0}", changedEntityNames.Aggregate((e1, e2) => string.Format("{0}, {1}", e1, e2))))

using var dbContext = new MyDbContext();
var objectToDelete = await dbContext.Objects.FirstAsync(o => o.Id == id);
dbContext.Objects.Remove(objectToDelete);
await dbContext.SaveChangesAsync();

using var dbContext = new MyDbContext();
var objectToDelete = new MyObject { Id = id };
dbContext.Objects.Remove(objectToDelete);
await dbContext.SaveChangesAsync();

await dbContext.Objects.Where(x => x.Id == id).ExecuteDeleteAsync();

public class User
{
    public int Id { get; set; }
    public required string FirstName { get; set; }
    public required string LastName { get; set; }
    public virtual List<Book> Books { get; set; } = new();
    public virtual Address? Address { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public required string Type { get; set; }
    public required string Name { get; set; }

    public virtual User User { get; set; } = default!;
    public int UserId { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public required string Street { get; set; }
    
    public virtual User User { get; set; } = default!;
    public int UserId { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

    public DbSet<User> Users { get; set; } = default!;

    public DbSet<Book> Books { get; set; } = default!;

    public DbSet<Address> Addresses { get; set; } = default!;
}

context.Books.Where(book => book.Name.Contains("1")).ExecuteDelete();

DELETE FROM [b]
FROM [Books] AS [b]
WHERE [b].[Name] LIKE N'%1%'

context.Users.Where(user => user.Id <= 500).ExecuteDelete();

DELETE FROM [u]
FROM [Users] AS [u]
WHERE [u].[Id] <= 500

The DELETE statement conflicted with the REFERENCE constraint "FK_Books_Users_UserId".
The conflict occurred in database "EF7BulkOperations", table "dbo.Books", column 'UserId'.

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

    public DbSet<User> Users { get; set; } = default!;

    public DbSet<Book> Books { get; set; } = default!;

    public DbSet<Address> Addresses { get; set; } = default!;

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

        modelBuilder
            .Entity<User>()
            .HasMany(user => user.Books)
            .WithOne(book => book.User)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder
            .Entity<User>()
            .HasOne(user => user.Address)
            .WithOne(address => address.User)
            .HasForeignKey<Address>(address => address.UserId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

context.Users.Where(user => user.Id <= 400)
   .ExecuteUpdate(p => p.SetProperty(user => user.LastName,  user => "Updated"));

UPDATE [u]
SET [u].[LastName] = N'Updated'
FROM [Users] AS [u]
WHERE [u].[Id] <= 400

UPDATE [u]
SET [u].[LastName] = N'Updated' + [u].[LastName]
FROM [Users] AS [u]
WHERE [u].[Id] <= 300

context.Users.Where(user => user.Id <= 800)
   .ExecuteUpdate(p => p.SetProperty(user => user.LastName,
        user => "Updated" + user.LastName)
             .SetProperty(user => user.FirstName,
                 user => "Updated" + user.FirstName));

UPDATE [u]
SET [u].[FirstName] = N'Updated' + [u].[FirstName],
[u].[LastName] = N'Updated' + [u].[LastName]
FROM [Users] AS [u]
WHERE [u].[Id] <= 800

The previews of EF Core 5.0 require .NET Standard 2.1. This means:

• EF Core 5.0 runs on .NET Core 3.1; it does not require .NET 5.
  • This may change in future previews depending on how the plan for .NET 5 evolves.
• EF Core 5.0 runs on other platforms that support .NET Standard 2.1.
• EF Core 5.0 will not run on .NET Standard 2.0 platforms, including .NET Framework. 

using System;
using PdfRpt.Core.Contracts;
using PdfRpt.FluentInterface;

namespace PdfReportSamples.MasterDetails
{
    public class MasterDetailsPdfReport
    {
        public IPdfReportData CreatePdfReport()
        {
            return new PdfReport().DocumentPreferences(doc =>
            {
                doc.RunDirection(PdfRunDirection.LeftToRight);
                doc.Orientation(PageOrientation.Portrait);
                doc.PageSize(PdfPageSize.A4);
                doc.DocumentMetadata(new DocumentMetadata { Author = "Vahid", Application = "PdfRpt", Keywords = "Test", Subject = "Test Rpt", Title = "Test" });
            })
            .DefaultFonts(fonts =>
            {
                fonts.Path(Environment.GetEnvironmentVariable("SystemRoot") + "\\fonts\\arial.ttf",
                                  Environment.GetEnvironmentVariable("SystemRoot") + "\\fonts\\verdana.ttf");
            })
            .PagesHeader(header =>
            {
                header.CustomHeader(new MasterDetailsHeaders { PdfRptFont = header.PdfFont });
            })
            .PagesFooter(footer =>
            {
                footer.DefaultFooter(DateTime.Now.ToString("MM/dd/yyyy"));
            })
            .MainTableTemplate(t => t.BasicTemplate(BasicTemplate.SilverTemplate))
            .MainTablePreferences(table =>
            {
                table.ColumnsWidthsType(TableColumnWidthType.Relative);
                table.GroupsPreferences(new GroupsPreferences
                {
                    GroupType = GroupType.HideGroupingColumns,
                    RepeatHeaderRowPerGroup = true,
                    ShowOneGroupPerPage = false,
                    SpacingBeforeAllGroupsSummary = 5f,
                    NewGroupAvailableSpacingThreshold = 170
                });
            })
            .MainTableDataSource(dataSource =>
            {
                dataSource.GenericDataReader(
                   providerName: "System.Data.SQLite",
                   connectionString: "Data Source=" + AppPath.ApplicationPath + "\\data\\blogs.sqlite",
                   sql: @"select 
                            tblParents.BirthDate as ParentBirthDate,
                            tblParents.Name as ParentName,
                            tblParents.LastName as ParentLastName,
                            tblKids.Name as KidName,
                            tblKids.BirthDate as KidBirthDate
                            from tblParents
                                left outer join tblKids
                                     on tblKids.ParentId = tblParents.Id
                            order by 
                                tblParents.Name,
                                tblParents.LastName,
                                tblKids.Name"
               );
            })
            .MainTableColumns(columns =>
            {
                columns.AddColumn(column =>
                {
                    column.PropertyName("rowNo");
                    column.IsRowNumber(true);
                    column.CellsHorizontalAlignment(HorizontalAlignment.Left);
                    column.IsVisible(true);
                    column.Order(0);
                    column.Width(1);
                    column.HeaderCell("#");
                });

                columns.AddColumn(column =>
                {
                    column.PropertyName("ParentBirthDate");
                    column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                    column.Order(1);
                    column.Width(2);
                    column.HeaderCell("ParentBirthDate");
                    column.Group(true,
                    (val1, val2) =>
                    {
                        var date1 = (DateTime)val1;
                        var date2 = (DateTime)val2;
                        return date1.Year == date2.Year && date1.Month == date2.Month && date1.Day == date2.Day;
                    });
                });

                columns.AddColumn(column =>
                {
                    column.PropertyName("ParentName");
                    column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                    column.Order(2);
                    column.Width(2);
                    column.HeaderCell("ParentName");
                    column.Group(true,
                    (val1, val2) =>
                    {
                        return val1.ToString() == val2.ToString();
                    });
                });

                columns.AddColumn(column =>
                {
                    column.PropertyName("ParentLastName");
                    column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                    column.Order(3);
                    column.Width(2);
                    column.HeaderCell("ParentLastName");
                    column.Group(true,
                    (val1, val2) =>
                    {
                        return val1.ToString() == val2.ToString();
                    });
                });

                columns.AddColumn(column =>
                {
                    column.PropertyName("KidName");
                    column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                    column.Order(4);
                    column.Width(2);
                    column.HeaderCell("Child Name");
                    column.IsVisible(true);
                });

                columns.AddColumn(column =>
                {
                    column.PropertyName("KidBirthDate");
                    column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                    column.Order(5);
                    column.Width(2);
                    column.HeaderCell("BirthDate");
                    column.IsVisible(true);
                });
            })
            .MainTableEvents(events =>
            {
                events.DataSourceIsEmpty(message: "There is no data available to display.");
            })
            .Export(e => e.ToExcel())
            .Generate(data => data.AsPdfFile(AppPath.ApplicationPath + "\\Pdf\\RptMasterDetailsSample.pdf"));
        }
    }
}

using System.Collections.Generic;
using iTextSharp.text;
using iTextSharp.text.pdf;
using PdfRpt.ColumnsItemsTemplates;
using PdfRpt.Core.Contracts;
using PdfRpt.Core.Helper;

namespace PdfReportSamples.MasterDetails
{
    public class MasterDetailsHeaders : IPageHeader
    {
        public IPdfFont PdfRptFont { set; get; }

        public PdfPTable RenderingGroupHeader(Document pdfDoc, PdfWriter pdfWriter, IList<CellData> newGroupInfo, IList<SummaryCellData> summaryData)
        {
            var parentName = newGroupInfo.GetSafeStringValueOf("ParentName");
            var parentLastName = newGroupInfo.GetSafeStringValueOf("ParentLastName");
            var parentBirthDate = newGroupInfo.GetSafeStringValueOf("ParentBirthDate");

            var table = new PdfPTable(relativeWidths: new[] { 1f, 5f }) { WidthPercentage = 100 };
            table.AddSimpleRow(
                (cellData, cellProperties) =>
                {
                    cellData.Value = "Name:";
                    cellProperties.PdfFont = PdfRptFont;
                    cellProperties.PdfFontStyle = DocumentFontStyle.Bold;
                    cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
                },
                (cellData, cellProperties) =>
                {
                    cellData.Value = parentName;
                    cellProperties.PdfFont = PdfRptFont;
                    cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
                });
            table.AddSimpleRow(
                (cellData, cellProperties) =>
                {
                    cellData.Value = "Last Name:";
                    cellProperties.PdfFont = PdfRptFont;
                    cellProperties.PdfFontStyle = DocumentFontStyle.Bold;
                    cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
                },
                (cellData, cellProperties) =>
                {
                    cellData.Value = parentLastName;
                    cellProperties.PdfFont = PdfRptFont;
                    cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
                });
            table.AddSimpleRow(
               (cellData, cellProperties) =>
               {
                   cellData.Value = "Birth Date:";
                   cellProperties.PdfFont = PdfRptFont;
                   cellProperties.PdfFontStyle = DocumentFontStyle.Bold;
                   cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
               },
               (cellData, cellProperties) =>
               {
                   cellData.Value = parentBirthDate;
                   cellProperties.PdfFont = PdfRptFont;
                   cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
               });
            return table.AddBorderToTable(borderColor: BaseColor.LIGHT_GRAY, spacingBefore: 5f);
        }

        public PdfPTable RenderingReportHeader(Document pdfDoc, PdfWriter pdfWriter, IList<SummaryCellData> summaryData)
        {
            var table = new PdfPTable(numColumns: 1) { WidthPercentage = 100 };
            table.AddSimpleRow(
               (cellData, cellProperties) =>
               {
                   cellData.CellTemplate = new ImageFilePathField();
                   cellData.Value = AppPath.ApplicationPath + "\\Images\\01.png";
                   cellProperties.HorizontalAlignment = HorizontalAlignment.Center;
               });
            table.AddSimpleRow(
               (cellData, cellProperties) =>
               {
                   cellData.Value = "Family rpt";
                   cellProperties.PdfFont = PdfRptFont;
                   cellProperties.PdfFontStyle = DocumentFontStyle.Bold;
                   cellProperties.HorizontalAlignment = HorizontalAlignment.Center;
               });
            return table.AddBorderToTable();
        }
    }
}

select 
          tblParents.BirthDate as ParentBirthDate,
          tblParents.Name as ParentName,
          tblParents.LastName as ParentLastName,
          tblKids.Name as KidName,
          tblKids.BirthDate as KidBirthDate
          from tblParents
                  left outer join tblKids
                        on tblKids.ParentId = tblParents.Id
                            order by 
                                tblParents.Name,
                                tblParents.LastName,
                                tblKids.Name

tblParents.Name as ParentName,
tblKids.Name as KidName,

column.Group(true,
                    (val1, val2) =>
                    {
                        return val1.ToString() == val2.ToString();
                    });

                table.GroupsPreferences(new GroupsPreferences
                {
                    GroupType = GroupType.HideGroupingColumns,
                    RepeatHeaderRowPerGroup = true,
                    ShowOneGroupPerPage = false,
                    SpacingBeforeAllGroupsSummary = 5f,
                    NewGroupAvailableSpacingThreshold = 170
                });

        public PdfPTable RenderingGroupHeader(Document pdfDoc, PdfWriter pdfWriter, IList<CellData> newGroupInfo, IList<SummaryCellData> summaryData)
        {
            var parentName = newGroupInfo.GetSafeStringValueOf("ParentName");
            var parentLastName = newGroupInfo.GetSafeStringValueOf("ParentLastName");
            var parentBirthDate = newGroupInfo.GetSafeStringValueOf("ParentBirthDate");

            var table = new PdfPTable(relativeWidths: new[] { 1f, 5f }) { WidthPercentage = 100 };
            table.AddSimpleRow(
                (cellData, cellProperties) =>
                {
                    cellData.Value = "Name:";
                    cellProperties.PdfFont = PdfRptFont;
                    cellProperties.PdfFontStyle = DocumentFontStyle.Bold;
                    cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
                },
                (cellData, cellProperties) =>
                {
                    cellData.Value = parentName;
                    cellProperties.PdfFont = PdfRptFont;
                    cellProperties.HorizontalAlignment = HorizontalAlignment.Left;
                });

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int MemId { set; get; }
        public virtual Member Member { set; get; }

       // ...
    }
}

var startTimes = context.Bookings
                        .Where(booking => booking.Member.FirstName == "David"
                                            && booking.Member.Surname == "Farrell")
                        .Select(booking => new { booking.StartTime })
                        .ToList();

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int FacId { set; get; }
        public virtual Facility Facility { set; get; }

       // ...
    }
}

int[] tennisCourts = { 0, 1 };
var date1 = new DateTime(2012, 09, 21);
var date2 = new DateTime(2012, 09, 22);
var startTimes = context.Bookings
                        .Where(booking => tennisCourts.Contains(booking.Facility.FacId)
                                && booking.StartTime >= date1
                                && booking.StartTime < date2)
                        .Select(booking => new { booking.StartTime, booking.Facility.Name })
                        .ToList();

namespace EFCorePgExercises.Entities
{
    public class Member
    {
       // ...

        public virtual ICollection<Member> Children { get; set; }
        public virtual Member Recommender { set; get; }
        public int? RecommendedBy { set; get; }

       // ...
    }
}

var members = context.Members
                        .Where(member => member.Recommender != null)
                        .Select(member => new { member.Recommender.FirstName, member.Recommender.Surname })
                        .Distinct()
                        .OrderBy(member => member.Surname).ThenBy(member => member.FirstName)
                        .ToList();

var members = context.Members
                        .Select(member => new
                        {
                            memFName = member.FirstName,
                            memSName = member.Surname,
                            recFName = member.Recommender.FirstName ?? "",
                            recSName = member.Recommender.Surname ?? ""
                        })
                        .OrderBy(member => member.memSName).ThenBy(member => member.memFName)
                        .ToList();

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int FacId { set; get; }
        public virtual Facility Facility { set; get; }

        public int MemId { set; get; }
        public virtual Member Member { set; get; }

       // ...
    }
}

namespace EFCorePgExercises.Entities
{
    public class Member
    {
       // ...

        public virtual ICollection<Booking> Bookings { set; get; }
    }
}

int[] tennisCourts = { 0, 1 };
var members = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => tennisCourts.Contains(booking.Facility.FacId))
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name
                        })
                        .Distinct()
                        .OrderBy(x => x.Member)
                        .ToList();

var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);

var items = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2
                        && (
                            (((booking.Slots * booking.Facility.GuestCost) > 30) && (booking.MemId == 0)) ||
                            (((booking.Slots * booking.Facility.MemberCost) > 30) && (booking.MemId != 0))
                        ))
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name,
                            Cost = booking.MemId == 0 ?
                                        booking.Slots * booking.Facility.GuestCost
                                        : booking.Slots * booking.Facility.MemberCost
                        })
                        .Distinct()
                        .OrderByDescending(x => x.Cost)
                        .ToList();

var members = context.Members
                        .Select(member =>
                        new
                        {
                            Member = member.FirstName + " " + member.Surname,
                            Recommender = context.Members
                                .Where(recommender => recommender.MemId == member.RecommendedBy)
                                .Select(recommender => recommender.FirstName + " " + recommender.Surname)
                                .FirstOrDefault() ?? ""
                        })
                        .Distinct()
                        .OrderBy(member => member.Member)
                        .ToList();

var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);

var items = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2)
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name,
                            Cost = booking.MemId == 0 ?
                                        booking.Slots * booking.Facility.GuestCost
                                        : booking.Slots * booking.Facility.MemberCost
                        })
                        .Where(x => x.Cost > 30)
                        .Distinct()
                        .OrderByDescending(x => x.Cost)
                        .ToList();

Earlier this week, Microsoft published the roadmap for .NET Core 2.1, ASP.NET Core 2.1 and EF Core 2.1, expected to be out in the first quarter of 2018. The team also talked about several new features with this new release. This release is more of a feedback-oriented release based on .NET Core 2.0 release. The.NET Core 2.0 is a huge success and already more than half a million developers are now using .NET Core 2.0. All thanks to .NET Standard 2.0 release . In this post find out about the new features of .NET Core 2.1.