Category: Sql Server

Xml DataType With Entity Framework

Giridhar PrakashLeave a comment

Requirement: Need to save an object collection in Sql Server Xml data type column.
Note: In Sql Server 2016 JSON support was added.

Steps:
1. Employee and Address classes declared.
2. Created EmployeeMapping of type EntityTypeConfiguration which is used by DbContext.
Note: Addresses collection should be ignored in mapping to avoid key required error on Address Entity.

Note: it’s not full proof code.

namespace EntityFrameworkXmlDemo
{
internal class Program
{
public static void Main(string[] args)
{
Console.WriteLine("Welcome");
bool retrieve = true;
var context = new SampleContext("connection string here");
if (retrieve)
{
var employee = context.Employees.FirstOrDefault(e => e.Id == 1);
var addresses = employee.Addresses;
}
else
{
var employee = new Employee()
{
Name = "Fanstastic"
};
employee.Addresses.Add(new Address()
{
Name = "Newyork",
Zip = 1234
});
employee.Addresses.Add(new Address()
{
Name = "California",
Zip = 7658
});
employee.SerializeAddress();
context.Employees.Add(employee);
context.SaveChanges();
}
Console.ReadKey();
}
}
public class SampleContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
public SampleContext(string connectionString) : base(connectionString)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new EmployeeMapping());
base.OnModelCreating(modelBuilder);
}
}
public class EmployeeMapping : EntityTypeConfiguration<Employee>
{
public EmployeeMapping()
{
this.ToTable("Employees");
this.HasKey(d => d.Id);
this.Property(d => d.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(d => d.Name);
this.Property(d => d.Address).HasColumnType("xml");
this.Ignore(d => d.Addresses);
}
}
public class Employee
{
private IList<Address> _addresses;
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public IList<Address> Addresses
{
get
{
_addresses = DeSerializeAddress();
return _addresses;
}
set => _addresses = value;
}
public void SerializeAddress()
{
var serializer = new XmlSerializer(typeof(List<Address>));
var stringWriter = new StringWriter();
serializer.Serialize(stringWriter, _addresses);
Address = stringWriter.ToString();
}
public IList<Address> DeSerializeAddress()
{
if (!string.IsNullOrEmpty(Address))
{
var serializer = new XmlSerializer(typeof(List<Address>));
var stringReader = new StringReader(Address);
return (IList<Address>)serializer.Deserialize(stringReader);
}
return _addresses;
}
public Employee()
{
_addresses = new List<Address>();
}
}
public class Address
{
public string Name { get; set; }
public int Zip { get; set; }
}
}
view raw Program.cs hosted with ❤ by GitHub

Using SQL Variables inside query in clause

Giridhar PrakashLeave a comment

Problem: I need to use a variable which should be used inside the in clause.


Case 1:
DECLARE @customerIds VARCHAR(200) = '123,456,789'
SELECT * From Customers Where Id in (????)
Case 2:
DECLARE @customerNames VARCHAR(200) = 'abc, def, ghi'
SELECT * From Customers Where FirstName in (????)

Solution Case 1:
Since already have comma separated integers, we should parse the variable as integer table.


CREATE FUNCTION [dbo].[StringSplitInt]
(
	@Idlist varchar(800),
	@delim char(1)
)
RETURNS @returntable TABLE
(
	Id int not null
)
AS
BEGIN
	DECLARE @list varchar(800) = RTRIM(LTRIM(@Idlist)) + @delim;
	DECLARE @idString varchar(10), @id INT;
	WHILE (LEN(@list) > 0)
	BEGIN
		SET @idString = SUBSTRING(@list, 1, CHARINDEX(@delim, @list,1)-1);
		SET @list = SUBSTRING(@list, LEN(@idString)+2, LEN(@list) - LEN(@idString)-1)
		INSERT @returntable(Id)
		SELECT CAST(@idString AS INT)
	END
	RETURN
END

Go

DECLARE @customerIds VARCHAR(200) = '123,456,789'
SELECT * From Customers Where Id in (SELECT Id from StringSplitInt(@customerIds))

Solution for Case 2:
Now we parse the string variable to table


CREATE FUNCTION [dbo].[StringSplitString]
(
	@Idlist varchar(800),
	@delim char(1)
)
RETURNS @returntable TABLE
(
	Id VARCHAR(MAX) COLLATE Latin1_General_CI_AI
)
AS
BEGIN
	DECLARE @list varchar(800) = RTRIM(LTRIM(@Idlist)) + @delim;
	DECLARE @idString varchar(100);
	WHILE (LEN(@list) > 0)
	BEGIN
		SET @idString = SUBSTRING(@list, 1, CHARINDEX(@delim, @list,1)-1);
		SET @list = SUBSTRING(@list, LEN(@idString)+2, LEN(@list) - LEN(@idString)-1)
		INSERT @returntable(Id)
		SELECT @idString
	END
	RETURN
END

Go

DECLARE @customerNames VARCHAR(200) = 'abc, def, ghi'
SELECT * From Customers Where FirstName in (Select Id from StringSplitString(@customerNames))

Note: You might get Collation mismatch error between the FirstName and Id Column returned by function. (I need to read a little more about this). I solved this problem by defining function column with our SQL Server collation setting.