CODEWITHSUNDEEP
c#entity-frameworkasp.net-web-api
G. Nah
G. Nah

Reputation: 11

Retrieving multiple entities from EF with dynamic parameters

I'm using Web API 2 in combination with the Entity Framework. I want to retrieve multiple entities with a controller method. I extract the parameters from the request header. Then I convert it into an two-dimensional array which contains the name and the value. Then I want to get all matching entities from the db. I already tried it with the SqlQuery() method in combination with a parameter array, but that doesn't work either.

I get an error:

System.Data.Entity.Core.EntityCommandExecutionException`
The datareader is not compatible with the given value. A element of the type ('ADRESSE1') does not have a corresponding column in the data reader of the same name.

This is my model:

public partial class ADRESSE
{
    public int id { get; set; }
    public string ADRESSE1 { get; set; }
    public string PERSONAL { get; set; }
    public string LIEFERADR { get; set; }
    public string RECHNUNGADR { get; set; }
    public string FIRMA { get; set; }
    public string FIRMA1 { get; set; }
    ...
}

This is the table in the database. (Because of the project client I can't change any naming in the db)

CREATE TABLE [dbo].[ADRESSE]
(
    [id] [int] NOT NULL,
    [ADRESSE] [nvarchar](15) NOT NULL,
    [PERSONAL] [nvarchar](31) NULL,
    [LIEFERADR] [nvarchar](15) NULL,
    [RECHNUNGADR] [nvarchar](15) NULL,
    [FIRMA] [nvarchar](15) NULL,
    [FIRMA1] [nvarchar](63) NULL,
    ...
)

My query:

[HttpGet]
[AcceptVerbs("GET", "HEAD")]
public IQueryable<ADRESSE> GetADRESSE()
{
   var header = HeaderExtractionProvider.GetRequestHeaders(Request.Headers);
   if (header.Count > 0)
   {
      var paramArray = new List<SqlParameter>();
      foreach (var param in header)
      {
         paramArray.Add(new SqlParameter(param[0], param[1]));
      }

      var test = db.ADRESSE.SqlQuery("Select * from ADRESSE", paramArray.ToArray());
      //return all found adresses here.
   }
   return db.ADRESSE;
}

Upvotes: 1

Views: 270

Answers (2)

Ehsan Ullah Nazir
Ehsan Ullah Nazir

Reputation: 1917

Query as follows with Linq

  var addressed = db.Addresses.Where(ad => paramArray.Contains(ad.Id));

Upvotes: 0

krillgar
krillgar

Reputation: 12805

The database error that you're getting is because your property name ADRESSE1 differs from your database column of ADRESSE. It doesn't know how to map that.

You can fix the issue with Entity Framework by using the ColumnAttribute found in System.ComponentModel.DataAnnotations.Schema.

public partial class ADRESSE
{
    public int id { get; set; }
    [Column("ADRESSE")]
    public string ADRESSE1 { get; set; }
    public string PERSONAL { get; set; }
    public string LIEFERADR { get; set; }
    public string RECHNUNGADR { get; set; }
    public string FIRMA { get; set; }
    public string FIRMA1 { get; set; }
    ...
}

You can then create a LINQ query like this:

var addresses = db.Addresses.Where(ad => paramArray.Contains(ad.Id));

Upvotes: 1

Related Questions