CODEWITHSUNDEEP
c#sqllinqlinq-to-sql
Ole Albers
Ole Albers

Reputation: 9285

Unable to determine SQL type for... when creating tables with LinQ

I got the following code:

public class SqlData : DataContext
{
    public Table<TextbausteinTyp> TextbausteinTypen;
    public SqlData(string connectionString) : base(connectionString)    {      }

    public void CreateDb()
    {
       CreateDatabase();
    }
}
[Table(Name = "tqTextbausteinTyp")]
public class TextbausteinTyp
{
    [Column(IsPrimaryKey = true, DbType = "BigInt IDENTITY NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]
    public int Id;
    [Column]
    public string Name;
}

[Table(Name = "tqTextbaustein")]
public class Textbaustein
{
    [Column(IsPrimaryKey = true, DbType = "BigInt IDENTITY NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]
    public int Id;
    [Column]
    public TextbausteinTyp IdBausteintyp;
    [Column]
    public string Inhalt;
    [Column]
    public string Name;
}

As you can see, it is quite simple. When executing CreateDatabase() I receive the following error:

Unable to determine SQL type for 'tqOrder.data.TextbausteinTyp'

The Connection string leads to a MSSQL-Database which is created by this code (but without the table)

What is going wrong here?

Upvotes: 1

Views: 1354

Answers (2)

Jamleck
Jamleck

Reputation: 1037

The error is trying to tell you that LinqToSQL is not able to determine what SQL Server type is should store objects of type TextbausteinTyp.

LINQ to SQL doesn't support user-defined types - only primitive types. It matches the CLR types with a particular SQL Server type as defined here.

That said, I can infer that you are trying to define an association between Textbaustein and TextbausteinTyp. You can use the AssociationAttribute to do this. Below is a modified code sample on how to do this. You might need to reverse the association.

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LinqConsoleApp
{
    public class SqlData : DataContext
    {
        public Table<TextbausteinTyp> TextbausteinTypen;
        public Table<Textbaustein> Textbausteins;

        public SqlData(string connectionString)
            : base(connectionString)
        {
        }
    }

    [Table(Name = "tqTextbausteinTyp")]
    public class TextbausteinTyp
    {
        [Column(IsPrimaryKey = true, DbType = "BigInt IDENTITY NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]
        public int Id;

        [Column]
        public string Name;
    }

    [Table(Name = "tqTextbaustein")]
    public class Textbaustein
    {
        [Column(IsPrimaryKey = true, DbType = "BigInt IDENTITY NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]
        public int Id;

        private EntityRef<TextbausteinTyp> _TextbausteinTyp;

        [Association(Storage = "_TextbausteinTyp", ThisKey = "Id")]
        public TextbausteinTyp IdBausteintyp;

        [Column]
        public string Inhalt;

        [Column]
        public string Name;
    }

    internal class Program
    {
        private static void Main(string[] args)
        {

            var builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            builder["Data Source"] = "<SERVERNAME>"
            builder["integrated Security"] = true;
            builder["Initial Catalog"] = "<DATABASE>";

            SqlData db = new SqlData(builder.ConnectionString);

            db.CreateDatabase();
            Console.ReadLine();
        }
    }
}

Upvotes: 2

Jon Hanna
Jon Hanna

Reputation: 113222

Unable to determine SQL type for 'tqOrder.data.TextbausteinTyp'

I'm not surprised, as I can't determine the SQL type for it either. How is a table meant to be a column in another table?

I imagine, but cannot be sure, that you actually want something like:

[Column(CanBeNull=false,DbType="int not null")]
public int IdBausteintyp;

So as to have a foreign-key column that references the IdBausteintyp column (I'd prefer to end with "id" rather than begin with it, but that's another matter).

You can from that starting point then add:

private EntityRef<TextbausteinTyp> _textbausteinTyp;
[Association(Storage = "_textbausteinTyp", ThisKey = "IdBausteintyp")]
public TextbausteinTyp TextbausteinTyp
{
    get { return _textbausteinTyp.Entity; }
    set { _textbausteinTyp.Entity = value; }
}

Then you have a TextbausteinTyp property that gets or sets a TextbausteinTyp related to the foreign key. (Note, you don't have to do this if you want to just explicitly join as necessary in your queries).

Likewise, to the TextbausteinTyp class you could choose to add:

private EntitySet<Textbaustein> _textbausteins;
[Association(Storage = "_textbausteins", OtherKey = "IdBausteintyp")]
public EntitySet<Textbaustein> Textbausteins
{
    get { return _textbausteins; }
    set { _textbausteins.Assign(value); }
}

So as to have the other side of this mapped; the collection of Textbaustein entities that are related to that particular TextbausteinTyp.

Upvotes: 3

Related Questions