none
Could not format node 'New' for execution as SQL error. RRS feed

  • Question

  • Hi,

     

    When executing the following LINQtoSQL I get a "System.InvalidOperationException: Could not format node 'New' for execution as SQL" error.

     

    I am attempting to contruct a domain object using the information from my sql/linq object.

     

    This is my linq query:

     

    Code Snippet

    return from m in _db.Messages

    select new Data.Message {

    Account =

    m.AccountId.HasValue

    ? new Data.Account { Id = m.AccountId.Value }

    : null,

    Body = m.Body,

    Id = m.MessageId,

    Subject = m.Subject

    };

     

     

    If I copy the query to LINQpad and referencing my domain object data dll it executes as expected.

     

    Am I doing something fundamentally wrong here or am I just missing something simple?

     

    Thanks

    Monday, September 15, 2008 4:43 PM

All replies

  • Linq to SQL does not allow you to directly create an Entity type. If either Data.Message or Data.Account are Entities then the framework will complain.  If _db.Messages consists of Data.Message already, then there is no need to create and fill the type yourself.  This would be sufficient:

     

    Code Snippet

    return from m in _db.Messages

           select m;

     

     

    I was unable to reproduce your error.  Could you please post additional information that would help in reproducing it?

    Friday, September 19, 2008 10:13 PM
  • Hi Josh,

     

    Thanks for your reply. Essentially what I am trying to do is create a loosely coupled domain object built up from the entity returned by the Linq query.

     

    In the above example, _db is my LinqDataContext which has been automatically generated by VS2008 from my database schema. _db.Messages is the Messages table in my database which in this case has a nullable int column called AccountId. So, messages may or may not be related to an account. In my domain model, I have a class called Message, which has a property of type Account, called Account. In my example my domain objects are prefixed with the Data namespace.

     

    A simplified version of my domain objects are as follows:

     

    Code Snippet

    namespace MyApp.Data {
        public class Message {
            public int Id { get; set; }
            public Account Account { get; set; }
            public Contact Sender { get; set; {
        }

     

        public class Account {
            public int Id { get; set; }
        }

     

        public class Contact {
            public int Id { get; set; }
        }
    }

     

    What I want to do is check the value of the _db.Messages.AccountId column. If it is null, assign my domain Message object Account property to null. If it is not null, create an Account object and populate Account.Id with the Message.AccountId int.

     

    I am doing something similar with non-null fields. For example lets say in the database the Message table also has a column called ContactId of type int. I can create the following linq query to return Message objects with an attached Contact object filled with the ContactId from the database:

     

    Code Snippet

    public List GetMessagesWithContact() {
        return (from m in _db.Messages
                select new Data.Message {
                    Contact = new Data.Contact {
                        Id = m.ContactId
                    }
                }).ToList();
    }

     

    This works as intended without any error mesages. The error seems to occur with the introduction of the ternary operator as in the below example:

     

    Code Snippet

    public List<Data.Message> GetMessagesWithAccount() {

        return (from m in _db.Messages

                select new Data.Message {

    Account = m.AccountId.HasValue

    ? new Data.Account { Id = m.AccountId }

    : null

      }).ToList();

    }

     

    I hope that explains the scenario a little more clearly. Please excuse any minor syntax errors as I am typing this directly into IE.

     

    Thanks!

    Saturday, September 20, 2008 8:59 PM
  • I was facing the same problem today, but I ended up redesigning my database a bit to make it work like i wanted it to.

    Rickard
    Sunday, September 21, 2008 1:03 PM