locked
I can use LINQ to SQL to Select from tables with SqlGeometry types, but Insert fails without throwing an exception. What's happening? RRS feed

  • Question

  • Hi folks,

    It seems I may have found a work around to get Linq to SQL to working for querying tables, but inserting or updating records fails.  First, the Linq 2 SQL designer doesn't support UDTs, but defining your own mappings seems to work (but like I said, just for queries).  For example, consider the following code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.Types;
    using System.Data.Linq.Mapping;
    
    namespace Example.Domain.Entity
    {
        [Table(Name = "Counties")]
        public class County
        {
            [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
            public int ID { get; set; }
    
            [Column(Name = "COUNTY_NAM")]
            public string Name { get; set; }
    
            [Column(Name = "G")]
            public SqlGeometry Geometry { get; set; }
        }
    }



    Then, following the repository pattern you can create an interface to the Counties table as such:

    using System.Linq;
    using Example.Domain.Entity;
    
    namespace Example.Domain
    {
        public interface ICountiesRepository
        {
            IQueryable<County> Counties { get; }
            void AddCounty(County county);
        }
    }
    
    
    You can define a SqlCountiesRepository as such:

    using System.Data.Linq;
    using System.Linq;
    using Example.Domain.Entity;
    namespace Example.Domain { public class SqlCountiesRepository : ICountiesRepository { private Table<County> _countiesTable; public SqlCountiesRepository(string connectionString) { _countiesTable = new DataContext(connectionString).GetTable<County>(); } public IQueryable<County> Counties { get { return _countiesTable; } } public void AddCounty(County county) { _countiesTable.InsertOnSubmit(county); _countiesTable.Context.SubmitChanges(); } } }
    Consumer code could do

    public List<County> CountiesInState(State state)
    {
        SqlCountiesRepository repository = new SqlCountiesRepository(@"connectionstring");
        return (from c in repository.Counties
                where c.Geometry.STOverlaps(state.Geometry).Value
                select c).ToList();
    }
    
    
    This all works fine, the problem is when I try to call AddCounty. 

    SqlCountiesRepository repository = new SqlCountiesRepository(@"connectionstring");
    repository.AddCounty(new County { 
        Name = "Madison",
        Geometry = SqlGeometry.STPolyFromText(new SqlChars(new SqlString(someWellKnownText)), 0) 
    });
    

    The record inserts fine and no exceptions are thrown, but the geometry field is null.  I've ensured that the WKT is in proper format and the geometry is valid.  Any ideas what is happening?
    Tuesday, January 19, 2010 8:12 AM

All replies

  • Is this L2S or EF?
    Thursday, January 27, 2011 2:37 PM
  • This is EF.

    Thursday, January 27, 2011 3:06 PM
  • Any idea if this technique could be used in L2S?
    Thursday, January 27, 2011 3:11 PM
  • I don't think either technologies support this at this time.  I've only been listing to EF chat but it sounds like its on the roadmap for EF anyway.
    Thursday, January 27, 2011 3:45 PM