Largest FREE Microsoft eBook Giveaway! I’m Giving Away MILLIONS of FREE Microsoft eBooks again, including: Windows 10, Office 365, Office 2016, Power BI, Azure, Windows 8.1, Office 2013, SharePoint 2016, SharePoint 2013, Dynamics CRM, PowerShell, Exchange Server, System Center, Cloud, SQL Server and more!
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) }
);
}
} namespace EFCoreDbFunctionsSample.DataLayer
{
public class ApplicationDbContext : DbContext
{
// ...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.HasDbFunction(SqlDbFunctionsExtensions.SqlDateDiffMethodInfo)
.HasTranslation(args =>
{
var parameters = args.ToArray();
var param0 = ((SqlConstantExpression)parameters[0]).Value.ToString();
return SqlFunctionExpression.Create("DATEDIFF",
new[]
{
new SqlFragmentExpression(param0), // It should be written as DateDiff(day, ...) and not DateDiff(N'day', ...) .
parameters[1],
parameters[2]
},
SqlDbFunctionsExtensions.SqlDateDiffMethodInfo.ReturnType,
typeMapping: null);
});
}
}
} var sinceDays = 10;
users = context.People.Where(person =>
SqlDbFunctionsExtensions.SqlDateDiff(SqlDateDiff.Day, person.AddDate, DateTime.Now) <= sinceDays).ToList();
/*
SELECT [p].[Id], [p].[AddDate], [p].[Name]
FROM [People] AS [p]
WHERE DATEDIFF(Day, [p].[AddDate], GETDATE()) <= @__sinceDays_0
*/ Use master;
SELECT name, compatibility_level FROM sys.databases
-- Old way:
EXEC sp_dbcmptlevel 'AdventureWorks', 100
-- New way:
ALTER DATABASE 'AdventureWorks' SET COMPATIBILITY_LEVEL = 100 ;
SELECT 'ALTER DATABASE ' + NAME + ' SET COMPATIBILITY_LEVEL = 100;' TSQL
FROM sys.databases
WHERE compatibility_level <> 100
CREATE TABLE xml_tab ( id INT, xml_col XML )
DECLARE @xml_tab TABLE (xml_col XML)
-- document
INSERT @xml_tab VALUES ('<person/>')
-- fragment
INSERT @xml_tab VALUES ('<person/><person/>')
SELECT * FROM @xml_tab DECLARE @xml_tab TABLE (xml_col XML)
-- text only
INSERT @xml_tab VALUES ('data data data .....')
-- empty string
INSERT @xml_tab VALUES ('')
-- null value
INSERT @xml_tab VALUES (null)
SELECT * FROM @xml_tab DECLARE @xml_tab TABLE (xml_col XML)
INSERT @xml_tab VALUES ('<doc/>')
INSERT @xml_tab VALUES ('<doc xmlns="http://www.doctors.com"/>')
-- این سه سطر در عمل یکی هستند
INSERT @xml_tab VALUES ('<doc xmlns="http://www.documents.com"/>')
INSERT @xml_tab VALUES ('<dd:doc xmlns:dd="http://www.documents.com"/>')
INSERT @xml_tab VALUES ('<rr:doc xmlns:rr="http://www.documents.com"/>')
SELECT * FROM @xml_tab DECLARE @xml_tab TABLE (id INT, xml_col XML) INSERT INTO @xml_tab VALUES ( 5, N'<?xml version="1.0" encoding="utf-8"?> <doc1> <row name="vahid"></row> </doc1> ')
XML parsing: line 1, character 38, unable to switch the encoding
DECLARE @xml_tab TABLE (id INT, xml_col XML) INSERT INTO @xml_tab VALUES ( 5, '<?xml version="1.0" encoding="utf-8"?><doc1><row name="vahid"></row></doc1>' ) SELECT * FROM @xml_tab
<doc1> <row name="vahid" /> </doc1>
DECLARE @xml_tab TABLE (id INT, xml_col XML) INSERT INTO @xml_tab VALUES ( 5, '<person><![CDATA[ 3 > 2 ]]></person>' ) SELECT * FROM @xml_tab
<person> 3 > 2 </person>
Generating HTML from SQL Server Queries
The versions affected are : Microsoft Exchange Server 2013, Microsoft Exchange Server 2016 , Microsoft Exchange Server 2019
SQL SERVER – Vertical Select Mode feature in SQL Server Management Studio
When I look at that list today, SQL Server 2016 makes a pretty compelling case for most folks. It’s a good balance of new features, stability, and long shelf life.
Scott Klein and Joey D'Antoni provide an overview of the SQL Server 2016 features and enhancements, teasing up many of the features discussed in the other videos as well as covered in the training kit.
using System.Xml.Linq;
namespace TestModel
{
public class DynamicTable
{
public virtual int Id { get; set; }
public virtual XDocument Document { get; set; }
}
}
using FluentNHibernate.Automapping;
using FluentNHibernate.Automapping.Alterations;
using NHibernate.Type;
namespace TestModel
{
public class DynamicTableMapping : IAutoMappingOverride<DynamicTable>
{
public void Override(AutoMapping<DynamicTable> mapping)
{
mapping.Id(x => x.Id);
mapping.Map(x => x.Document).CustomType<XDocType>();
}
}
}
if exists (select * from dbo.sysobjects
where id = object_id(N'[DynamicTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [DynamicTable]
create table [DynamicTable] (
Id INT IDENTITY NOT NULL,
Document XML null,
primary key (Id)
)
//insert
object savedId = 0;
using (var session = sessionFactory.OpenSession())
{
using (var tx = session.BeginTransaction())
{
var obj = new DynamicTable
{
Document = System.Xml.Linq.XDocument.Parse(
@"<Doc><Node1>Text1</Node1><Node2>Text2</Node2></Doc>"
)
};
savedId = session.Save(obj);
tx.Commit();
}
}
//simple query
using (var session = sessionFactory.OpenSession())
{
using (var tx = session.BeginTransaction())
{
var entity = session.Get<DynamicTable>(savedId);
if (entity != null)
{
Console.WriteLine(entity.Document.Root.ToString());
}
tx.Commit();
}
}
//advanced query
using (var session = sessionFactory.OpenSession())
{
using (var tx = session.BeginTransaction())
{
var list = session.CreateSQLQuery("select [Document].value('(//Doc/Node1)[1]','nvarchar(255)') from [DynamicTable] where id=:p0")
.SetParameter("p0", savedId)
.List();
if (list != null)
{
Console.WriteLine(list[0]);
}
tx.Commit();
}
}
