I am getting this error when I run an integration test against my Postgresql DB using a stored procedure.
Result Message: System.InvalidOperationException : Sequence contains more than one element
Here is the repo file:
public card_view SelectView(int card_id)
{
using (var connection = new NpgsqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@card_id", card_id);
using (var multi = connection.QueryMultiple("f_card_select_view", p, commandType: CommandType.StoredProcedure))
{
card_view view = multi.Read<card_view>().Single();
view.Categories = multi.Read<category>().ToList();
view.Modifiers = multi.Read<card_modifier_view>().ToList();
return view;
}
}
}
The card test file:
[Test]
public void SelectViewTest()
{
var repo = new CardRepository();
var result = repo.SelectView(31); // witch
Assert.AreEqual(2, result.Categories.Count);
Assert.AreEqual(2, result.Modifiers.Count);
}
Cardview file:
public class card_view
{
public int card_id { get; set; }
public int cardset_id { get; set; }
public string cardset_title { get; set; }
public string image_path { get; set; }
public string cardset_name { get; set; }
public int card_cost { get; set; }
public List<card_modifier_view> Modifiers { get; set; }
public List<category> Categories { get; set; }
}
cardmodifierview file:
public class card_modifier_view
{
public int card_modifier_id { get; set; }
public int card_id { get; set; }
public int modifier_type_id { get; set; }
public int? modifier_value { get; set; }
public string instruction_text { get; set; }
public string modifier_type_name { get; set; }
}
The DB function (The original SQL was T-SQL and I have done my best to translate it into regular SQL for postgres.)
CREATE FUNCTION f_card_select_view (card_id int)
RETURNS TABLE(card_id bigint, modifier_type_id integer,
instruction_text integer, modifier_type_name integer, card_modifier character varying, modifier_type character varying)
AS $$
SELECT card_id,cardset.cardset_id,card_title,image_path,card_cost,cardset_name
FROM card
INNER JOIN cardset ON card.cardset_id = cardset.cardset_id
WHERE card_id = @card_id;
SELECT card.category_id,category_name
FROM category card
INNER JOIN card_category ON card.category_id = card_category.category_id
WHERE card_category.card_id = @card_id;
SELECT f_card_modifier_selectby_card_id (@card_id);
$$ LANGUAGE sql;
Here is f_card_modifier_selectby_card_id:
CREATE FUNCTION f_card_modifier_selectby_card_id(card_id int)
RETURNS TABLE(
card_id bigint,
modifier_type_id int,
instruction_text int,
modifier_type_name int,
card_modifier varchar,
modifier_type varchar
)
AS $$
SELECT
card_modifier_id,
card_id,
card_modifier.modifier_type_id,
modifier_value,
instruction_text,
modifier_type_name
FROM card_modifier INNER JOIN modifier_type ON card_modifier.modifier_type_id = modifier_type.modifier_type_id
WHERE card_id = card_id
$$ LANGUAGE sql;
If the error is Sequence contains more than one element
, then the problem is that your first query (where you have the .Single()
) is returning more than one row. I can't tell you why that is, but you need to try running:
SELECT card_id,cardset.cardset_id,card_title,image_path,card_cost,cardset_name
FROM card
INNER JOIN cardset ON card.cardset_id = cardset.cardset_id
WHERE card_id = @card_id;
(with your expected @card_id
) in your SQL toolkit to see what happens. Or better: call the stored procedure itself - presumably via:
EXEC f_card_select_view {your id here}
It looks like your statement brings back a join containing multiple records for card_view or 0.
In other frameworks certainly you can use SingleOrDefault() to allow for 0 records. Note this will still error for multiple records.
If its getting multiple records you need to establish if this is correct and rework it to be a collection like you have done with .ToList() or correct your data in the database and possibly your keys.