Linq to SQL optional column, sql generated field

Question

I have a property in my Linq to SQL class that doesn't actually exist in my database.

I only populate this column during one query (namely for ranked searching)

            IEnumerable<BookingType> results = db.ExecuteQuery<BookingType>
            (
            "(SELECT  mm.rcount AS SearchRank, 
            b.* FROM (SELECT m.TourID AS myId, COUNT(m.RecordType) AS rcount 
            FROM (((SELECT * FROM Bookings h WHERE h.RecordType = 'H' 
                    AND h.TourArea like '%bull%')
            union
            (SELECT * FROM Bookings t WHERE t.RecordType = 'T' and t.TourGuideName like '%bull%')) ) m group by m.TourID) mm 
            INNER JOIN Bookings b ON mm.myId= b.TourID WHERE b.RecordType = 'H')", "bull"
            );

Heres part of the model:

    /// <summary>
    /// Unique identifier for a booking
    /// </summary>
    [DataMember]
    [Column(DbType = "int", IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true)]
    public int? BookingID { get; set; }

    /// <summary>
    /// Search Ranking
    /// </summary>
    [DataMember]
    [Column(DbType = "int")]
    public int? SearchRank { get; set; }

When I run this against my ranked search query it works, very well indeed. When I run any other queries I get the error 'SearchRank field does not exist', because it doesn't exist. If I put the SearchRank field in the database then the field comes back null when doing ranked search (presumably because the b.* portion of my query returns SearchRank too.

Is there a way to set some sort of 'OptionalColumn' attribute on my model?

Other wise I'll have to put the SearchRank column in the DB and then write out each individual column names for the RankedSearch query.

user1781678 · Accepted Answer · 2015-01-02 08:03:00Z

1

(I'm not sure if this will work)

Add virtual to your SearchRank property, and remove the column attribute from BookingType

e.g. public virtual int? SearchRank { get; set; }

Create a new class "BookingType2" which inherits BookingType

In the new Class override the SearchRank property and add the Column attribute.

Change the signature of your execute method to BookingType2

e.g. db.ExecuteQuery<BookingType2>(...

If it works you should be able to cast the result back to BookingType.

answered Jan 2, 2015 at 8:03

user1781678

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Linq to SQL optional column, sql generated field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related