SELECT * FROM table
ORDER BY NEWID()

using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

namespace Sample
{
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<User> Users { get; set; }
    }

    public class Configuration : DbMigrationsConfiguration<MyContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(MyContext context)
        {
            context.Users.Add(new User { Name = "User 1", Age = 20 });
            context.Users.Add(new User { Name = "User 2", Age = 25 });
            context.Users.Add(new User { Name = "User 3", Age = 30 });
            context.Users.Add(new User { Name = "User 4", Age = 35 });
            context.Users.Add(new User { Name = "User 5", Age = 40 });
            base.Seed(context);
        }
    }

    public static class Test
    {
        public static void RunTests()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());

            using (var context = new MyContext())
            {
               var randomListOfUsers =
                        context.Users
                               .Where(person => person.Age >= 25 && person.Age < 40)
                               .OrderBy(person => Guid.NewGuid())
                               .ToList();

               foreach (var person in randomListOfUsers)
                   Console.WriteLine("{0}:{1}", person.Name, person.Age);
            }
        }
    }
}

.OrderBy(person => Guid.NewGuid())

ORDER BY NEWID()

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name], 
[Project1].[Age] AS [Age]
FROM ( SELECT 
NEWID() AS [C1], ------ Guid created here
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Age] AS [Age]
FROM [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[Age] >= 25) AND ([Extent1].[Age] < 40)
)  AS [Project1]
ORDER BY [Project1].[C1] ASC  ------ Used for sorting here

Request #   2: GET     - 3,818 ms - <system>   - 200 - /indexes/dynamic/Questions?&query=Title%3ARaven*&pageSize=128

using System;
using System.Linq;
using Raven.Client.Document;
using RavenDBSample01.Models;
using Raven.Client;
using Raven.Client.Linq;
using Raven.Client.Indexes;

namespace RavenDBSample01
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var store = new DocumentStore
            {
                Url = "http://localhost:8080"
            }.Initialize())
            {
                store.DatabaseCommands.PutIndex(
                name: "Questions/ByTitle",
                indexDef: new IndexDefinitionBuilder<Question>
                {
                    Map = questions => questions.Select(question => new { Title = question.Title } )
                });
            }
        }
    }
}

    public class QuestionsByTitle : AbstractIndexCreationTask<Question>
    {
        public QuestionsByTitle()
        {
            Map = questions => questions.Select(question => new { Title = question.Title });
        }
    }

            using (var store = new DocumentStore
            {
                Url = "http://localhost:8080"
            }.Initialize())
            {
                IndexCreation.CreateIndexes(typeof(QuestionsByTitle).Assembly, store);
            }

            using (var store = new DocumentStore
            {
                Url = "http://localhost:8080"
            }.Initialize())
            {
                using (var session = store.OpenSession())
                {
                    var questions = session.Query<Question>(indexName: "QuestionsByTitle")
                                           .Where(x => x.Title.StartsWith("Raven")).Take(128);
                    foreach (var question in questions)
                    {
                        Console.WriteLine(question.Title);
                    }
                }
            }

Request # 147: GET     -    58 ms - <system>   - 200 - /indexes/QuestionsByTitle?&query=Title%3ARaven*&pageSize=128
        Query: Title:Raven*
        Time: 7 ms
        Index: QuestionsByTitle
        Results: 2 returned out of 2 total.

                    var questions = session.Query<Question, QuestionsByTitle>()
                                           .Where(x => x.Title.StartsWith("Raven")).Take(128);

    public class QuestionsCountByTitleReduceResult
    {
        public string Title { set; get; }
        public int Count { set; get; }
    }

    public class QuestionsCountByTitle : AbstractIndexCreationTask<Question, QuestionsCountByTitleReduceResult>
    {
        public QuestionsCountByTitle()
        {
            Map = questions => questions.Select(question =>
                                                    new
                                                    {
                                                        Title = question.Title,
                                                        Count = 1
                                                    });
            Reduce = results => results.GroupBy(x => x.Title)
                                       .Select(g =>
                                                   new
                                                   {
                                                       Title = g.Key,
                                                       Count = g.Sum(x => x.Count)
                                                   });
        }
    }

            using (var store = new DocumentStore
            {
                Url = "http://localhost:8080"
            }.Initialize())
            {
                IndexCreation.CreateIndexes(typeof(QuestionsCountByTitle).Assembly, store);

                using (var session = store.OpenSession())
                {
                    var result = session.Query<QuestionsCountByTitleReduceResult, QuestionsCountByTitle>()
                                         .FirstOrDefault(x => x.Title == "Raven") ?? new QuestionsCountByTitleReduceResult();
                    Console.WriteLine(result.Count);
                }
            }

 CREATE TABLE cd.members
    (
       memid integer NOT NULL, 
       surname character varying(200) NOT NULL, 
       firstname character varying(200) NOT NULL, 
       address character varying(300) NOT NULL, 
       zipcode integer NOT NULL, 
       telephone character varying(20) NOT NULL, 
       recommendedby integer,
       joindate timestamp not null,
       CONSTRAINT members_pk PRIMARY KEY (memid),
       CONSTRAINT fk_members_recommendedby FOREIGN KEY (recommendedby)
            REFERENCES cd.members(memid) ON DELETE SET NULL
    );

   CREATE TABLE cd.facilities
    (
       facid integer NOT NULL, 
       name character varying(100) NOT NULL, 
       membercost numeric NOT NULL, 
       guestcost numeric NOT NULL, 
       initialoutlay numeric NOT NULL, 
       monthlymaintenance numeric NOT NULL, 
       CONSTRAINT facilities_pk PRIMARY KEY (facid)
    );

CREATE TABLE cd.bookings
    (
       bookid integer NOT NULL, 
       facid integer NOT NULL, 
       memid integer NOT NULL, 
       starttime timestamp NOT NULL,
       slots integer NOT NULL,
       CONSTRAINT bookings_pk PRIMARY KEY (bookid),
       CONSTRAINT fk_bookings_facid FOREIGN KEY (facid) REFERENCES cd.facilities(facid),
       CONSTRAINT fk_bookings_memid FOREIGN KEY (memid) REFERENCES cd.members(memid)
    );

namespace EFCorePgExercises.Entities
{
    public class Member
    {
        public int MemId { set; get; }

        public string Surname { set; get; }

        public string FirstName { set; get; }

        public string Address { set; get; }

        public int ZipCode { set; get; }

        public string Telephone { set; get; }

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

        public DateTime JoinDate { set; get; }

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

namespace EFCorePgExercises.Entities
{
    public class Facility
    {
        public int FacId { set; get; }

        public string Name { set; get; }

        public decimal MemberCost { set; get; }

        public decimal GuestCost { set; get; }

        public decimal InitialOutlay { set; get; }

        public decimal MonthlyMaintenance { set; get; }

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

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
        public int BookId { set; get; }

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

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

        public DateTime StartTime { set; get; }

        public int Slots { set; get; }
    }
}

namespace EFCorePgExercises.Entities
{
    public class MemberConfiguration : IEntityTypeConfiguration<Member>
    {
        public void Configure(EntityTypeBuilder<Member> builder)
        {
            builder.HasKey(member => member.MemId);
            builder.Property(member => member.MemId).IsRequired().UseIdentityColumn(seed: 0, increment: 1);

            builder.Property(member => member.Surname).HasMaxLength(200).IsRequired();
            builder.Property(member => member.FirstName).HasMaxLength(200).IsRequired();
            builder.Property(member => member.Address).HasMaxLength(300).IsRequired();
            builder.Property(member => member.ZipCode).IsRequired();
            builder.Property(member => member.Telephone).HasMaxLength(20).IsRequired();

            builder.HasIndex(member => member.RecommendedBy);
            builder.HasOne(member => member.Recommender)
                    .WithMany(member => member.Children)
                    .HasForeignKey(member => member.RecommendedBy);

            builder.Property(member => member.JoinDate).IsRequired();

            builder.HasIndex(member => member.JoinDate).HasName("IX_JoinDate");
            builder.HasIndex(member => member.RecommendedBy).HasName("IX_RecommendedBy");
        }
    }
}

namespace EFCorePgExercises.Entities
{
    public class FacilityConfiguration : IEntityTypeConfiguration<Facility>
    {
        public void Configure(EntityTypeBuilder<Facility> builder)
        {
            builder.HasKey(facility => facility.FacId);
            builder.Property(facility => facility.FacId).IsRequired().UseIdentityColumn(seed: 0, increment: 1);

            builder.Property(facility => facility.Name).HasMaxLength(100).IsRequired();

            builder.Property(facility => facility.MemberCost).IsRequired().HasColumnType("decimal(18, 6)");

            builder.Property(facility => facility.GuestCost).IsRequired().HasColumnType("decimal(18, 6)");

            builder.Property(facility => facility.InitialOutlay).IsRequired().HasColumnType("decimal(18, 6)");

            builder.Property(facility => facility.MonthlyMaintenance).IsRequired().HasColumnType("decimal(18, 6)");
        }
    }
}

namespace EFCorePgExercises.Entities
{
    public class BookingConfiguration : IEntityTypeConfiguration<Booking>
    {
        public void Configure(EntityTypeBuilder<Booking> builder)
        {
            builder.HasKey(booking => booking.BookId);
            builder.Property(booking => booking.BookId).IsRequired().UseIdentityColumn(seed: 0, increment: 1);

            builder.Property(booking => booking.FacId).IsRequired();
            builder.HasOne(booking => booking.Facility)
                    .WithMany(facility => facility.Bookings)
                    .HasForeignKey(booking => booking.FacId);

            builder.Property(booking => booking.MemId).IsRequired();
            builder.HasOne(booking => booking.Member)
                    .WithMany(member => member.Bookings)
                    .HasForeignKey(booking => booking.MemId);

            builder.Property(booking => booking.StartTime).IsRequired();

            builder.Property(booking => booking.Slots).IsRequired();

            builder.HasIndex(booking => new { booking.MemId, booking.FacId }).HasName("IX_memid_facid");
            builder.HasIndex(booking => new { booking.FacId, booking.StartTime }).HasName("IX_facid_starttime");
            builder.HasIndex(booking => new { booking.MemId, booking.StartTime }).HasName("IX_memid_starttime");
            builder.HasIndex(booking => booking.StartTime).HasName("IX_starttime");
        }
    }
}

namespace EFCorePgExercises.DataLayer
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions options)
            : base(options)
        {
        }

        public DbSet<Member> Members { get; set; }

        public DbSet<Booking> Bookings { get; set; }

        public DbSet<Facility> Facilities { get; set; }

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

            modelBuilder.ApplyConfigurationsFromAssembly(typeof(MemberConfiguration).Assembly);
        }
    }
}

dotnet tool install --global dotnet-ef --version 3.1.6
dotnet tool update --global dotnet-ef --version 3.1.6
dotnet build
dotnet ef migrations add Init --context ApplicationDbContext

context.Facilities.Add(new Facility { Name = "Tennis Court 1", MemberCost = 5, GuestCost = 25, InitialOutlay = 10000, MonthlyMaintenance = 200 });
// مابقی موارد
context.SaveChanges();

context.Members.Add(new Member { ... });
context.SaveChanges(); // For id = 0 = Int's CLR Default Value!

using (var transaction = context.Database.BeginTransaction())
{
    try
    {
        context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT Members ON");

        context.Members.Add(new Member { ... });
        // مابقی موارد

        context.SaveChanges();

        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
    finally
    {
        context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT Members OFF");
    }
}

1. به صورت لیست
2. به صورت لیست sort شده براساس Name
3. فیلتر بر روی لیستی از فیلدهای موجود
4. این  لیست باید شامل تمامی داده‌های موجود (چه در رم و چه در دیتابیس) باشد.

    private static void GetLocalDestinationCount()
    {
        using (var context = new BreakAwayContext())
        {
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Destinations in memory: {0}", count);
        }
    }

    private static void GetLocalDestinationCount()
    {
        using (var context = new BreakAwayContext())
        {
            foreach (var destination in context.Destinations)
            {
                Console.WriteLine(destination.Name);
            }
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Destinations in memory: {0}", count);
        }
    }

    private static void GetLocalDestinationCountWithLoad()
    {
        using (var context = new BreakAwayContext())
        {
            context.Destinations.Load();
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Destinations in memory: {0}", count);
        }
    }

    private static void LoadAustralianDestinations()
    {
        using (var context = new BreakAwayContext())
        {
            var query = from d in context.Destinations
                        where d.Country == "Australia"
                        select d;
            query.Load();
            var count = context.Destinations.Local.Count;
            Console.WriteLine("Aussie destinations in memory: {0}", count);
        }
    }

    private static void LocalLinqQueries()
    {
        using (var context = new BreakAwayContext())
        {
            context.Destinations.Load();
            var sortedDestinations = from d in context.Destinations.Local
                                     orderby d.Name
                                     select d;
            Console.WriteLine("All Destinations:");
            foreach (var destination in sortedDestinations)
            {
                Console.WriteLine(destination.Name);

            }
        }
        var aussieDestinations = from d in context.Destinations.Local
                                 where d.Country == "Australia"
                                 select d;
        Console.WriteLine();
        Console.WriteLine("Australian Destinations:");
        foreach (var destination in aussieDestinations)
        {
            Console.WriteLine(destination.Name);
        }
    }

تفاوت بین Linq provider‌های مختلف:

$"Users: {Users.Count()}".Dump();
$"Logins: {Logins.Count()}".Dump();

Users: 1200
Logins: 21000

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using EFReplaceAll.Models;

namespace EFReplaceAll.Config
{
    public class DbSetInfo
    {
        public IQueryable<object> DbSet { set; get; }
        public Type DbSetType { set; get; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Product> Products { set; get; }
        public DbSet<Category> Categories { set; get; }
        public DbSet<User> Users { set; get; }

        public MyContext()
            : base("Connection1")
        {
            this.Database.Log = sql => Console.Write(sql);
        }

        public IList<DbSetInfo> GetAllDbSets()
        {
            return this.GetType()
                .GetProperties()
                .Where(p => p.PropertyType.IsGenericType &&
                            p.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>))
                .Select(p => new DbSetInfo
                {
                    DbSet = (IQueryable<object>)p.GetValue(this, null),
                    DbSetType = p.PropertyType.GetGenericArguments().First()
                })
                .ToList();
        }
    }
}

using System;

namespace EFReplaceAll.Utils
{
    public class ReplaceOp
    {
        public string ToFind { set; get; }
        public string ToReplace { set; get; }
        public StringComparison Comparison { set; get; }
    }
}

using System.Collections.Generic;
using System.Linq;
using EFReplaceAll.Config;

namespace EFReplaceAll.Utils
{
    public static class UpdateDbContextContents
    {
        public static void ReplaceAllStringsAcrossTables(IList<ReplaceOp> replaceOps)
        {
            int dbSetsCount;
            using (var uow = new MyContext())
            {
                dbSetsCount = uow.GetAllDbSets().Count;
            }

            for (var i = 0; i < dbSetsCount; i++)
            {
                using (var uow = new MyContext()) // using a new context each time to free resources quickly.
                {
                    var dbSetResult = uow.GetAllDbSets()[i];
                    var stringProperties = dbSetResult.DbSetType.GetProperties()
                        .Where(p => p.PropertyType == typeof(string))
                        .ToList();
                    var dbSetEntities = dbSetResult.DbSet;
                    var haveChanges = false;
                    foreach (var entity in dbSetEntities)
                    {
                        foreach (var stringProperty in stringProperties)
                        {
                            var oldPropertyValue = stringProperty.GetValue(entity, null) as string;
                            if (string.IsNullOrWhiteSpace(oldPropertyValue))
                            {
                                continue;
                            }

                            var newPropertyValue = oldPropertyValue;
                            foreach (var replaceOp in replaceOps)
                            {
                                newPropertyValue = newPropertyValue.ReplaceString(replaceOp.ToFind, replaceOp.ToReplace, replaceOp.Comparison);
                            }
                            if (oldPropertyValue != newPropertyValue)
                            {
                                stringProperty.SetValue(entity, newPropertyValue, null);
                                haveChanges = true;
                            }
                        }
                    }

                    if (haveChanges)
                    {
                        uow.SaveChanges();
                    }
                }
            }
        }

    }
}

using System;
using System.Text;

namespace EFReplaceAll.Utils
{
    public static class StringExtensions
    {
        public static string ReplaceString(this string src, string oldValue, string newValue, StringComparison comparison)
        {
            if (string.IsNullOrWhiteSpace(src))
            {
                return src;
            }

            if (string.Compare(oldValue, newValue, comparison) == 0)
            {
                return src;
            }

            var sb = new StringBuilder();

            var previousIndex = 0;
            var index = src.IndexOf(oldValue, comparison);

            while (index != -1)
            {
                sb.Append(src.Substring(previousIndex, index - previousIndex));
                sb.Append(newValue);
                index += oldValue.Length;

                previousIndex = index;
                index = src.IndexOf(oldValue, index, comparison);
            }

            sb.Append(src.Substring(previousIndex));

            return sb.ToString();
        }
    }
}

            UpdateDbContextContents.ReplaceAllStringsAcrossTables(
                new[]
                {
                    new ReplaceOp
                    {
                        ToFind = "https://www.dntips.ir",
                        ToReplace = "https://www.dntips.ir",
                        Comparison = StringComparison.OrdinalIgnoreCase
                    },
                    new ReplaceOp
                    {
                        ToFind = "https://www.dntips.ir",
                        ToReplace = "https://www.dntips.ir",
                        Comparison = StringComparison.OrdinalIgnoreCase
                    }
                });