How to add list to SQL query?

Question

I pass a parameter month and append to a list.

How can I add the list to a SqlQuery parameter?

Error: No mapping exists from object type System.Collections.Generic.List`1[] to a known managed provider native type.

public ActionResult filterhre(int? month, int? year)
{
    List<int> lst = new List<int>();

    for (int i = 1; i <= @month; i++)
    {
        lst.Add(i);
    }

    ViewBag.location_fraud_year = db.Database.SqlQuery<YearCheck>(@"SELECT fraud_location, count(claim_amount) as counting_claim, sum(claim_amount) as counting_sum FROM testtable
where month(datelocation) in ({0}) and year(datelocation)={1} and fraud_location is not null ",lst, year).ToList(); 
}

Model:

public class YearCheck
{
    public string fraud_location { get; set; }
    public int? counting_claim { get; set; }
    public decimal? counting_sum { get; set; }
}

What is the type of `location_fraud_year` ? Do you have an implicit conversion to convert from `List` to it's type? — Chris, Dec 10 '18 at 20:21
https://stackoverflow.com/questions/4981390/convert-a-list-to-a-string-in-c-sharp — WillardSolutions, Dec 10 '18 at 20:25

score 0 · Accepted Answer · answered Dec 10 '18 at 20:35

As the error message is telling you, .Net does not know how to convert your list to a comma separated list in your SQL. You'll have to do that by yourself:

ViewBag.location_fraud_year = db.Database.SqlQuery<YearCheck>(@"...", String.Join(",", names.ToArray()), year).ToList();

How to add list to SQL query?

1 Answers1