The entity or complex type 'FPSDB_newModel.Form_Attachment' cannot be constructed in a LINQ to Entities query

Question

I am using Entity Framework DB first approach. When I want to pull the data from DB I use the method GetForm_AttachmentByAppID.I get the following run time error when I call this method from my code:

An exception of type 'System.NotSupportedException' occurred in     EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The entity or complex type 'FPSDB_newModel.Form_Attachment' cannot be constructed in a LINQ to Entities query.

public partial class Form_Attachment
{
public int Form_AttachmentID { get; set; }
public Nullable<int> AttachmentCategoryID { get; set; }
public int ApplicationID { get; set; }
public string EmployeeID { get; set; }
public string DocumentName { get; set; }
public string DocumentSize { get; set; }
public Nullable<byte> RoleID { get; set; }
public string Description { get; set; }
public Nullable<bool> SelectionForExtRev { get; set; }

public virtual Application Application { get; set; }
public virtual AttachmentCategory AttachmentCategory { get; set; }
public virtual Employee Employee { get; set; }
public virtual Role Role { get; set; }
}    

The above file is auto-generated and the code is present in FPSModel.cs while below is my code to pull data

public List<Form_Attachment> GetForm_AttachmentByAppID(int applicationID)
{
    var context = new FPSDB_newEntities();
    var data = (from f in context.Form_Attachment
                where
                (
                f.ApplicationID== applicationID
                )
                select new Form_Attachment
                {
                    Form_AttachmentID = f.Form_AttachmentID,
                    AttachmentCategoryID = f.AttachmentCategoryID,
                    EmployeeID = f.EmployeeID,
                    ApplicationID = f.ApplicationID,
                    DocumentName = f.DocumentName,
                    DocumentSize = f.DocumentSize,
                    RoleID = f.RoleID,
                    Description = f.Description,
                    SelectionForExtRev = f.SelectionForExtRev
                }).ToList();
    return data;
}

Also here is the DB definition of my table Form_Attachments

CREATE TABLE [dbo].[Form_Attachment](
[Form_AttachmentID] [int] IDENTITY(1,1) NOT NULL,
[AttachmentCategoryID] [int] NULL,
[ApplicationID] [int] NOT NULL,
[EmployeeID] [varchar](10) NOT NULL,
[DocumentName] [nvarchar](500) NULL,
[DocumentSize] [nvarchar](50) NULL,
[RoleID] [tinyint] NULL,
[Description] [ntext] NULL,
[SelectionForExtRev] [bit] NULL,
 CONSTRAINT [PK_Form_AttachmentID] PRIMARY KEY CLUSTERED 
(
[Form_AttachmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have read this The Projection should not be on to a mapped entity So if i make a class Form_Attachments1 just like auto-generated Form_Attachments so will it be a Data Transfer object DTO and is it a right approach.

Answer 1

You can try this:

public List<Form_Attachment> GetForm_AttachmentByAppID(int applicationID)
{
    var context = new FPSDB_newEntities();
    var data = (from f in context.Form_Attachment
                where
                (
                f.ApplicationID== applicationID
                )
                select f).ToList();
    return data;
}

or this:

data = context.Form_Attachment.Where(p => p.ApplicationID == applicationID).ToList();

I can't test it now, but this should work. You don't need to map because 'f' means your 'Form_Attachment' entity class already. If you were using a DTO class like 'Form_AttachmentDto', then you would need to map it like this:

public List<Form_AttachmentDto> GetForm_AttachmentByAppID(int applicationID)
    {
        var context = new FPSDB_newEntities();
        var data = (from f in context.Form_Attachment
                    where
                    (
                    f.ApplicationID== applicationID
                    )
                    select f).Select(p=>new Form_AttachmentDto() 
                        { 
                        //...
                        // here comes the mapping code
                        //..
                        }
                    ).ToList();
        return data;
    }