Xml DataType With Entity Framework

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.

Using SQL Variables inside query in clause

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.