private static void ClintEastwoodAsAnActorInLinqSyntax(CollectionEntities context)
{
var query = context.tDVD
.Where(p => p.tDVDxCast.Any(x => x.tCastAndCrew.FirstName == "Clint" && x.tCastAndCrew.LastName == "Eastwood"))
.OrderBy(p => p.SortTitle);
PrintProfiles(query);
}
private static void ClintEastwoodAsAnActorInQuerySyntax(CollectionEntities context)
{
var query = from p in context.tDVD
where p.tDVDxCast.Any(x => x.tCastAndCrew.FirstName == "Clint" && x.tCastAndCrew.LastName == "Eastwood")
orderby p.SortTitle
select p;
PrintProfiles(query);
}
private static void AllWritingsStaffInLinqSyntax(CollectionEntities context)
{
var query = context.tCreditType
.Where(ct => ct.Type == "Writing")
.SelectMany(ct => ct.tCreditSubtype)
.SelectMany(cst => cst.tDVDxCrew)
.Select(x => x.tCastAndCrew)
.Distinct()
.OrderBy(c => c.LastName).ThenBy(c => c.FirstName).ThenBy(c => c.MiddleName).ThenBy(c => c.BirthYear);
PrintCrew(query);
}
private static void BoughtIn2018InQuerySyntax(CollectionEntities context)
{
var jan1 = new DateTime(2018, 1, 1, 0, 0, 0);
var dec31 = new DateTime(2018, 12, 31, 23, 59, 59);
var query = from p in context.tPurchase
where p.Date >= jan1 && p.Date <= dec31 && p.PriceValue > 0
orderby p.Date, p.tDVD.SortTitle
select p.tDVD;
PrintProfiles(query);
}
private static void BoughtIn2018InLinqSyntax(CollectionEntities context)
{
var jan1 = new DateTime(2018, 1, 1, 0, 0, 0);
var dec31 = new DateTime(2018, 12, 31, 23, 59, 59);
var query = context.tPurchase
.Where(p => p.Date.Value >= jan1 && p.Date.Value <= dec31 && p.PriceValue > 0)
.OrderBy(p => p.Date).ThenBy(p => p.tDVD.SortTitle)
.Select(p => p.tDVD);
PrintProfiles(query);
}
private static void ClintEastwoodAsAnActorOrDirectorInLinqSyntax(CollectionEntities context)
{
var castQuery = context.tDVDxCast
.Where(x => x.tCastAndCrew.FirstName == "Clint" && x.tCastAndCrew.LastName == "Eastwood");
var crewQuery = context.tDVDxCrew
.Where(x => x.tCastAndCrew.FirstName == "Clint" && x.tCastAndCrew.LastName == "Eastwood" &&
x.tCreditSubtype.Subtype == "Director" && x.tCreditSubtype.tCreditType.Type == "Direction");
var query = context.tDVD
.Where(p => p.tDVDxCast.Any(x => castQuery.Contains(x)) || p.tDVDxCrew.Any(x => crewQuery.Contains(x)))
.OrderBy(p => p.SortTitle);
PrintProfiles(query);
}
private static void AllWritingsStaffInQuerySyntax(CollectionEntities context)
{
var query = from ct in context.tCreditType
where ct.Type == "Writing"
from cst in ct.tCreditSubtype
from x in cst.tDVDxCrew
select x.tCastAndCrew;
var distinct = query.Distinct(); //Query syntax doesn't have the distinct keyword
var ordered = from c in distinct
orderby c.LastName, c.FirstName, c.MiddleName, c.BirthYear
select c;
PrintCrew(ordered);
}
private static void ClintEastwoodAsAnActorOrDirectorInQuerySyntax(CollectionEntities context)
{
var castQuery = from x in context.tDVDxCast
where x.tCastAndCrew.FirstName == "Clint" && x.tCastAndCrew.LastName == "Eastwood"
select x;
var crewQuery = from x in context.tDVDxCrew
where x.tCastAndCrew.FirstName == "Clint" && x.tCastAndCrew.LastName == "Eastwood" &&
x.tCreditSubtype.Subtype == "Director" && x.tCreditSubtype.tCreditType.Type == "Direction"
select x;
var query = from p in context.tDVD
where p.tDVDxCast.Any(x => castQuery.Contains(x)) || p.tDVDxCrew.Any(x => crewQuery.Contains(x))
orderby p.SortTitle
select p;
PrintProfiles(query);
}
private static void Process(string mdfFile)
{
// "metadata=res://*/CollectionModel.csdl|res://*/CollectionModel.ssdl|res://*/CollectionModel.msl;provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\Collection.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework'"
var connectionString = $@"metadata=res://*/CollectionModel.csdl|res://*/CollectionModel.ssdl|res://*/CollectionModel.msl;provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\MSSQLLocalDB;attachdbfilename={mdfFile};integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;';";
using (var context = new CollectionEntities(connectionString))
{
context.Database.Log = Console.WriteLine; //this prints the SQL query to the console in the moment the SQL is actually executed against the database
//which means you can see how many queries are actually executed and what the SQL query looks like
BoughtIn2018InLinqSyntax(context);
Console.WriteLine();
BoughtIn2018InQuerySyntax(context);
Console.WriteLine();
Console.WriteLine();
ClintEastwoodAsAnActorInLinqSyntax(context);
Console.WriteLine();
ClintEastwoodAsAnActorInQuerySyntax(context);
Console.WriteLine();
Console.WriteLine();
ClintEastwoodAsAnActorOrDirectorInLinqSyntax(context);
Console.WriteLine();
ClintEastwoodAsAnActorOrDirectorInQuerySyntax(context);
Console.WriteLine();
Console.WriteLine();
AllWritingsStaffInLinqSyntax(context);
Console.WriteLine();
AllWritingsStaffInQuerySyntax(context);
Console.WriteLine();
}
Console.ReadLine();
}
public SupportLivreRepository(CollectionEntities context)
{
this.context = context;
}
public UtilisateurRepository(CollectionEntities context)
{
this.context = context;
}
internal ExceptionXml Process(string collectionFile, string mdfTargetFile)
{
ExceptionXml exceptionXml = null;
DbContextTransaction transaction = null;
try
{
CopyDatabaseFiles(mdfTargetFile);
var collection = DVDProfilerSerializer <Profiler.Collection> .Deserialize(collectionFile);
var profiles = collection.DVDList;
//Phase 2: Fill Hashtables
var cache = new CollectionCache(profiles);
// "metadata=res://*/CollectionModel.csdl|res://*/CollectionModel.ssdl|res://*/CollectionModel.msl;provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\Collection.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework'"
var connectionString = $@"metadata=res://*/CollectionModel.csdl|res://*/CollectionModel.ssdl|res://*/CollectionModel.msl;provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\MSSQLLocalDB;attachdbfilename={mdfTargetFile};integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;';";
using (_context = new CollectionEntities(connectionString))
{
//_context.Database.Log = Console.WriteLine;
_context.Configuration.AutoDetectChangesEnabled = false;
using (transaction = _context.Database.BeginTransaction())
{
CheckDbVersion();
//Phase 3: Fill Basic Data Into Database
var baseData = InsertBaseData(cache);
//Phase 4: Fill DVDs into Database
InsertData(profiles, baseData);
transaction.Commit();
}
}
Feedback?.Invoke(this, new EventArgs <string>($"{(profiles?.Length ?? 0):#,##0} profiles transformed."));
}
catch (Exception exception)
{
try
{
transaction.Rollback();
}
catch
{
}
try
{
if (File.Exists(mdfTargetFile))
{
File.Delete(mdfTargetFile);
}
}
catch
{
}
var ex = exception;
while (ex != null)
{
Feedback?.Invoke(this, new EventArgs <string>($"Error: {ex.Message} "));
ex = ex.InnerException;
}
exceptionXml = new ExceptionXml(exception);
}
return(exceptionXml);
}
public GenreFilmRepository(CollectionEntities context)
{
this.context = context;
}
public JeuxRepository(CollectionEntities context)
{
this.context = context;
}
public UtilisateurLivreSupportRepository(CollectionEntities context)
{
this.context = context;
}
