none
Straight SQL SELECT in Entity Framework RRS feed

  • Question

  • I'm trying to do a straight SQL Select statement under the latest EF (4.1 I believe?) since I have column names with spaces in them (and there doesn't seem to be a Linq workaround for that!) and besides, I want to learn how to do this as a straight, parameterized SELECT with more than one table.

    When I go into MS Studio, and execute the same statement, I get 12 valid records. When I execute the code in C#, below, however, I get 12 records but they are all filled with 0's. I must have something wrong in how I set this up - can someone please enlighten me here? Thanks, RVic

    class MasterPlanningRecord
        {
            public int releaseNumber;
            public int shipmentsRecordNumber;

            public String shipmentsPurchaseOrderNumber

            public int itemClosedAndShipped; //("bit" i.e. 0 Or 1)

    }

    //then, in another class, by statement...

               using (DSSEntities db = new DSSEntities())
                {
                    var sqlparam = new SqlParameter("PONumber", PONumber);
                    var sql = @"SELECT [Products].[ReleaseNumber]
                    ,[Shipments].[Record Number]
                    ,[Shipments].[Purchase Order Number],[Shipments].[Item closed and Shipped]
                  from [Shipments]
                  join [Products]
                  on [Shipments].[Purchase Order Number]= [Products].[Purchase Order Number]
                  where [Shipments].[Purchase Order Number]= @PONumber";            
                        var result = db.Database.SqlQuery<MasterPlanningRecord>(sql, sqlparam);                  
                        foreach(var r in result)
                        {
                            Console.WriteLine(r.releaseNumber + r.shipmentsRecordNumber+r.shipmentsPurchaseOrderNumber+r.itemClosedAndShipped);
                        }

    Friday, August 16, 2013 1:04 PM

Answers

  • I figured it out, and am posting here in case someone encoutners this in the future:

    I needed

    { get; set; }
    on the properties in the model AND in the statement, after each element to select, "as <model_parameter_name>"

    see below:

    class MasterPlanningRecord
        {
            public int releaseNumber  { get; set; }
            public int shipmentsRecordNumber  { get; set; }

            public String shipmentsPurchaseOrderNumber  { get; set; }

            public int itemClosedAndShipped  { get; set; }//("bit" i.e. 0 Or 1)

    }

    using (DSSEntities db = new DSSEntities())
                {
                    var sqlparam = new SqlParameter("PONumber", PONumber);
                    var sql = @"SELECT [Products].[ReleaseNumber] as releaseNumber
                    ,[Shipments].[Record Number] as shipmentsRecordNumber
                    ,[Shipments].[Purchase Order Number] as shipmentsPurchaseOrderNumber

    ...

    RVic

    Friday, August 16, 2013 3:30 PM

All replies

  • I figured it out, and am posting here in case someone encoutners this in the future:

    I needed

    { get; set; }
    on the properties in the model AND in the statement, after each element to select, "as <model_parameter_name>"

    see below:

    class MasterPlanningRecord
        {
            public int releaseNumber  { get; set; }
            public int shipmentsRecordNumber  { get; set; }

            public String shipmentsPurchaseOrderNumber  { get; set; }

            public int itemClosedAndShipped  { get; set; }//("bit" i.e. 0 Or 1)

    }

    using (DSSEntities db = new DSSEntities())
                {
                    var sqlparam = new SqlParameter("PONumber", PONumber);
                    var sql = @"SELECT [Products].[ReleaseNumber] as releaseNumber
                    ,[Shipments].[Record Number] as shipmentsRecordNumber
                    ,[Shipments].[Purchase Order Number] as shipmentsPurchaseOrderNumber

    ...

    RVic

    Friday, August 16, 2013 3:30 PM
  • Hi JavaRefugee,

    Thank you for sharing your  experience here. It will be very beneficial for other community members who have similar questions.

    Best Regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, August 19, 2013 1:18 AM
    Moderator