❔ filtering using dapper
Here is my function
public async Task<List<string>> GetNotExistBarcodes(IEnumerable<string> barcodes)
{
var param = new DynamicParameters();
param.AddTable(
parameterName: "@barcodes",
dataTableType: "[app].[udt_ProductCompare]",
values: barcodes);
var sql = $@"
SELECT bc.[Barcode]
FROM @barcodes AS bc
WHERE NOT EXISTS (
SELECT [Id]
FROM [app].[MasterProduct] AS mp
WHERE mp.[Barcode] = bc.[Barcode]
);
";
var query = await WithDbConnection(conn => conn
.QueryAsync<string>(
sql: sql,
param: param,
commandType: CommandType.Text));
return query.ToList();
}
here is the User defined Table Type
IF TYPE_ID('[app].[udt_ProductCompare]') IS NOT NULL
DROP TYPE [app].[udt_ProductCompare];
CREATE TYPE [app].[udt_ProductCompare] AS TABLE
(
[Barcode] nvarchar NOT NULL UNIQUE
)
am getting this error any one can help me out with this
"There are not enough fields in the Structured type. Structured types must have at least one field"
public async Task<List<string>> GetNotExistBarcodes(IEnumerable<string> barcodes)
{
var param = new DynamicParameters();
param.AddTable(
parameterName: "@barcodes",
dataTableType: "[app].[udt_ProductCompare]",
values: barcodes);
var sql = $@"
SELECT bc.[Barcode]
FROM @barcodes AS bc
WHERE NOT EXISTS (
SELECT [Id]
FROM [app].[MasterProduct] AS mp
WHERE mp.[Barcode] = bc.[Barcode]
);
";
var query = await WithDbConnection(conn => conn
.QueryAsync<string>(
sql: sql,
param: param,
commandType: CommandType.Text));
return query.ToList();
}
here is the User defined Table Type
IF TYPE_ID('[app].[udt_ProductCompare]') IS NOT NULL
DROP TYPE [app].[udt_ProductCompare];
CREATE TYPE [app].[udt_ProductCompare] AS TABLE
(
[Barcode] nvarchar NOT NULL UNIQUE
)
am getting this error any one can help me out with this
"There are not enough fields in the Structured type. Structured types must have at least one field"
