none
How do I specify columns defined by the SQL "AS" keyword in the Entity Data Model? RRS feed

  • Question

  • I have a SQL statement that contains an AS clause to define a resulting column in the query. For example:

    SELECT LAST_NAME, FIRST_NAME, TRIM(FIRST_NAME || ' ' || LAST_NAME) AS FULL_NAME FROM TABLE1

    Obviously FULL_NAME doesn't exist in the database, only in the query.  I tried adding the column FULL_NAME to the table in the entity data model, but when I ran the query I got the message:

    The data reader is incompatible with the specified 'Model.TABLE1'. A member of the type, 'FULL_NAME', does not have a corresponding column in the data reader with the same name.

    The SQL statement I'm actually using is much more complex, I'm just using this as an example.

    How do I define fields defined by an SQL AS clause in a table?

    I'm new to Entity Framework. . . :(

    Thursday, November 14, 2013 4:07 PM

Answers

  • <copied>

    I'm not authorized to make changes to the database, such as add columns to a table, create views, stored procedures, etc.

    What is a "projected property"? 

    <end>

    No one is telling you to make any changes to the database. When you run a Linq query,  either a single object or a collection of objects are returned as a result of the query.  The object's property name and its data in the property are based on the column name and the data in those columns that are from the Oracle database table.   

    You have had to have seen this at one time while you were using Linq and the ADO.NET Entity Framework.

    You are at this point using "0bjects" that have been returned to you. You can project that object to contain properties of your choice, or you can project the object to be anything you want it to be. The Oracle table and its data are out of the picture from the result of a Linq query and Object Oriented Programming.

    You have complete control of what the name of the property should be if you don't like the one derived from the Oracle table and change the name,  or you can make a new property in the object that is based on a concatenation of other properties that were returned from the Linq query. You can project the objects in the returned result to be anything you want them to be based what was pulled from the database table.

    I suggest that you stop and understand Linq Projection and Anonymous Types. It's Linq 101 and Object Oriented Programming 101 for you, and you need to figure it out. 

    http://msdn.microsoft.com/en-us/library/vstudio/bb397696.aspx

    http://www.c-sharpcorner.com/uploadfile/dhananjaycoder/linq-to-object-part-sharp1-projection/

    http://msdn.microsoft.com/en-us/library/bb738447(v=vs.110).aspx

    http://code.msdn.microsoft.com/LINQ-to-DataSets-09787825

    In the above links,  find them in VB use Bing or Google. But you need to figure out what Linq Projection can do for you. You are basically intercepting the objects with Linq Projection,  and *you* take control and tell Linq what you want the objects to be.

    http://msdn.microsoft.com/en-us/vstudio/bb688088.aspx

    Tuesday, November 19, 2013 9:45 PM

All replies

  • Hello Carol16,

    >>I tried adding the column FULL_NAME to the table in the entity data model?

    Do you mean that you add a scalar property in the designer model only?

    If it is, the answer is no. We must make sure that the table columns are one to one mapped to the entity properties.

    So we need to add the fullname column in database,too.

    And for achieving what you want, we can use the calculated column:

    We firstly need to create a function like below:

    CREATE FUNCTION [dbo].[GetFullName]( @FirstName varchar(50),     @LastName varchar(50)  )  RETURNS varchar(255)  AS BEGIN     DECLARE @FullName varchar(255);        SELECT @FullName = @FirstName + ' ' + @LastName;        RETURN @FullName;  END

    And we add the column to the table:

    alter table [dbo].[person] add [FullName] AS [dbo].[GetFullName]([FirstName],[LastName])

    And the result:

    CREATE TABLE [dbo].[Person] (
    
        [FirstName] NVARCHAR (50) NOT NULL,
    
        [LastName]  NVARCHAR (50) NOT NULL,
    
        [FullName]  AS            ([dbo].[GetFullName]([FirstName],[LastName])),
    
        PRIMARY KEY CLUSTERED ([FirstName] ASC)
    
    );
    

    And then the table can be imported to designer directly.

    Or if you do not want to add column in database and then we need to make effort on the query. We need to write the query as below:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    var result = (from person in db.People
    
                                  select new { person.FirstName, person.LastName, FullName = person.LastName + person.LastName }).ToList();
    
                }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 15, 2013 3:42 AM
    Moderator
  • The actual query is very complex, is there a way to do this using raw SQL?

    If I use the ToList() method, does it matter whether the resulting columns are defined in the Entity Data Model?

    The problem is, I want to use the result as input to Crystal Reports.  If the columns don't exist in the Data Model, how do I define references to them?

    I'm not authorized to make changes to the database, such as table columns, views, or stored procedures.

    I didn't have this problem using Microsoft .Net provider for Oracle and the Data Set Designer.

    I'm using VB .Net.

    We're using an Oracle database.

    Friday, November 15, 2013 8:47 PM
  • <copied>

    How do I define fields defined by an SQL AS clause in a table?

    I'm new to Entity Framework. . . :(

    <end>

    So why don't you use a Linq projection query using anonymous types where you can select the properties or not select properties or make up a new property that is based on selected properties in the query and project a new object. You can call the made up property name  anything you want to call it and project a new object that is not solely based on properties derived from database table columns in a query. 

    http://msdn.microsoft.com/en-us/library/bb738447(v=vs.110).aspx

    http://code.msdn.microsoft.com/LINQ-to-DataSets-09787825

    You can concatenate selected properties into a new projected property, do a projected new property for math a (totals property) based on selected numeric properties (adding) them to the new (totals property)  in the query and you can also call a function in code to return a value to a new projected property, doing it all on the fly to create the new projected object. 

    Saturday, November 16, 2013 7:18 AM
  • I'm not authorized to make changes to the database, such as add columns to a table, create views, stored procedures, etc.

    What is a "projected property"?  The only properties I see in the EDM are Scalar Property, Navigation Property, and Complex Property.

    I'm using the results of the query as input to Crystal Reports.  How can I reference fields that do not exist in the Entity Data Model?

    I did not have this problem with the .Net provider for Oracle and the Data Set Designer.

    Tuesday, November 19, 2013 4:09 PM
  • With EF you need a .NET type for the results of your query.  You can always run a SQL query using ADO.NET using the same connection.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, November 19, 2013 4:24 PM
  • Can you provide an example (using VB .Net)?

    Using the EF, how do I define fields that are the result of a query and are not in the original database?

    I want to use the result as input to Crystal Reports.

    Please forgive me . . . I'm new to Entity Framework.

    Tuesday, November 19, 2013 8:11 PM
  • <copied>

    I'm not authorized to make changes to the database, such as add columns to a table, create views, stored procedures, etc.

    What is a "projected property"? 

    <end>

    No one is telling you to make any changes to the database. When you run a Linq query,  either a single object or a collection of objects are returned as a result of the query.  The object's property name and its data in the property are based on the column name and the data in those columns that are from the Oracle database table.   

    You have had to have seen this at one time while you were using Linq and the ADO.NET Entity Framework.

    You are at this point using "0bjects" that have been returned to you. You can project that object to contain properties of your choice, or you can project the object to be anything you want it to be. The Oracle table and its data are out of the picture from the result of a Linq query and Object Oriented Programming.

    You have complete control of what the name of the property should be if you don't like the one derived from the Oracle table and change the name,  or you can make a new property in the object that is based on a concatenation of other properties that were returned from the Linq query. You can project the objects in the returned result to be anything you want them to be based what was pulled from the database table.

    I suggest that you stop and understand Linq Projection and Anonymous Types. It's Linq 101 and Object Oriented Programming 101 for you, and you need to figure it out. 

    http://msdn.microsoft.com/en-us/library/vstudio/bb397696.aspx

    http://www.c-sharpcorner.com/uploadfile/dhananjaycoder/linq-to-object-part-sharp1-projection/

    http://msdn.microsoft.com/en-us/library/bb738447(v=vs.110).aspx

    http://code.msdn.microsoft.com/LINQ-to-DataSets-09787825

    In the above links,  find them in VB use Bing or Google. But you need to figure out what Linq Projection can do for you. You are basically intercepting the objects with Linq Projection,  and *you* take control and tell Linq what you want the objects to be.

    http://msdn.microsoft.com/en-us/vstudio/bb688088.aspx

    Tuesday, November 19, 2013 9:45 PM
  •  <copied>

    I want to use the result as input to Crystal Reports.

    <end>

    You can Linq project out to a new object call it CrystalReportObj.

    It would be based on VB auto properties object like in the link.

    http://msdn.microsoft.com/en-us/library/vstudio/dd293589.aspx

    Public Class CrystalReportObj

    Public Property Firstname as string

    Public Property LastName as string

    Public Property FullName as string

    end class

    -------------------------------------

    Figure out how to tell a Linq Projection query that you did against as Oracle Table and tell Linq to project out a new CrystalReportObj

    Figure out how to the the Linq Projection query how to take the returned propulate the Fullname from a concatenation of Firstname and Lastname properties.  

    Crystal Reports can be defined and use a collection of CrystalReportObj(s).

    Tuesday, November 19, 2013 10:02 PM