none
LINQ without DBML files RRS feed

  • Question

  • Hi,

    Are DBML files are must when using LINQ? Is it possible to use LINQ without DBML files?

    If its possible, is there any example I can see?

    Thanks

    Friday, July 3, 2009 5:52 PM

Answers

  • Presumably you mean LINQ to SQL.

    You can certainly use LINQ to SQL without DBML files. All you need are classes decorated with the [Table] and [Column] attributes. For example, if you want to query a simple Customer table, define the following class:

    [Table] public class Customer
    {
      [Column(IsPrimaryKey=true)]  public int ID;
      [Column]                     public string Name;
    }
    

    Then you can start querying as follows:

       var db = new DataContext();
       var customers = db.GetTable<Customer>();
       var query = customers.Where (c => c.Name.StartsWith ("a"));

    You can even define assocation properties manually - the following creates classes for Customer and Purchase in a one:many relationship:

    public class DemoDataContext : DataContext
    {
      public DemoDataContext (string cxString) : base (cxString) { }
    
      public Table<Customer> Customers { get { return GetTable<Customer>(); } }
      public Table<Purchase> Purchases { get { return GetTable<Purchase>(); } }
    }
    
    [Table] public class Customer
    {
      [Column(IsPrimaryKey=true)]  public int ID;
      [Column]                     public string Name;
    
      [Association (OtherKey="CustomerID")]
      public EntitySet<Purchase> Purchases = new EntitySet<Purchase>();
    }
    
    [Table] public class Purchase
    {
      [Column(IsPrimaryKey=true)]  public int ID;
      [Column]                     public int CustomerID;
      [Column]                     public string Description;
      [Column]                     public decimal Price;
      [Column]                     public DateTime Date;
    
      EntityRef<Customer> custRef;
    
      [Association (Storage="custRef",ThisKey="CustomerID",IsForeignKey=true)]
      public Customer Customer
      {
        get { return custRef.Entity; } set { custRef.Entity = value; }
      }
    }
    

    Notice that we've also subclassed DataContext and added convience properties for Customers and Purchases. This is called a typed datacontext. If you want, you can write your own program to generate this - without ever needing a DBML file.

    Joe
    Write LINQ queries interactively - www.linqpad.net
    Saturday, July 4, 2009 1:28 AM
    Answerer
  • The DBML files are just used by the designer to allow you to tweak what code will be generated.

    You can instead write your own classes and either decorate them with the same attributes or use a .map file.

    Documentation on both is available on MSDN.

    [)amien
    • Marked as answer by Venkat Ganesh Saturday, July 4, 2009 5:50 PM
    Saturday, July 4, 2009 5:48 PM
    Moderator

All replies

  • Presumably you mean LINQ to SQL.

    You can certainly use LINQ to SQL without DBML files. All you need are classes decorated with the [Table] and [Column] attributes. For example, if you want to query a simple Customer table, define the following class:

    [Table] public class Customer
    {
      [Column(IsPrimaryKey=true)]  public int ID;
      [Column]                     public string Name;
    }
    

    Then you can start querying as follows:

       var db = new DataContext();
       var customers = db.GetTable<Customer>();
       var query = customers.Where (c => c.Name.StartsWith ("a"));

    You can even define assocation properties manually - the following creates classes for Customer and Purchase in a one:many relationship:

    public class DemoDataContext : DataContext
    {
      public DemoDataContext (string cxString) : base (cxString) { }
    
      public Table<Customer> Customers { get { return GetTable<Customer>(); } }
      public Table<Purchase> Purchases { get { return GetTable<Purchase>(); } }
    }
    
    [Table] public class Customer
    {
      [Column(IsPrimaryKey=true)]  public int ID;
      [Column]                     public string Name;
    
      [Association (OtherKey="CustomerID")]
      public EntitySet<Purchase> Purchases = new EntitySet<Purchase>();
    }
    
    [Table] public class Purchase
    {
      [Column(IsPrimaryKey=true)]  public int ID;
      [Column]                     public int CustomerID;
      [Column]                     public string Description;
      [Column]                     public decimal Price;
      [Column]                     public DateTime Date;
    
      EntityRef<Customer> custRef;
    
      [Association (Storage="custRef",ThisKey="CustomerID",IsForeignKey=true)]
      public Customer Customer
      {
        get { return custRef.Entity; } set { custRef.Entity = value; }
      }
    }
    

    Notice that we've also subclassed DataContext and added convience properties for Customers and Purchases. This is called a typed datacontext. If you want, you can write your own program to generate this - without ever needing a DBML file.

    Joe
    Write LINQ queries interactively - www.linqpad.net
    Saturday, July 4, 2009 1:28 AM
    Answerer
  • The DBML files are just used by the designer to allow you to tweak what code will be generated.

    You can instead write your own classes and either decorate them with the same attributes or use a .map file.

    Documentation on both is available on MSDN.

    [)amien
    • Marked as answer by Venkat Ganesh Saturday, July 4, 2009 5:50 PM
    Saturday, July 4, 2009 5:48 PM
    Moderator
  • The DBML files are just used by the designer to allow you to tweak what code will be generated.

    You can instead write your own classes and either decorate them with the same attributes or use a .map file.

    Documentation on both is available on MSDN.

    [)amien
    I am trying to execute the example given by Joe, Can you suggest any links?
    Saturday, July 4, 2009 5:49 PM
  • The attributes you can use to map an SQL Server database to your .NET object model are documented here .
    MVP XML My blog
    Sunday, July 5, 2009 11:23 AM
  • Hi there,

    DBML files are used as a design tool where we can drag/drop db objects and corresponding mapping classes are automatically generated for us.

    You can use LINQ to SQL without DBML file by using SqlMetal command. You can open Visual Studio command prompt and type sqlmetal.exe to find more about it. But please note that SqlMetal has some limitations (like if there are temporary tables used in any procedure, then you have to manually generate mapping class for it).

    I hope this solves your query.

    Thanks,
    H
    Monday, July 6, 2009 11:43 AM
  • Hi there,

    DBML files are used as a design tool where we can drag/drop db objects and corresponding mapping classes are automatically generated for us.

    You can use LINQ to SQL without DBML file by using SqlMetal command. You can open Visual Studio command prompt and type sqlmetal.exe to find more about it. But please note that SqlMetal has some limitations (like if there are temporary tables used in any procedure, then you have to manually generate mapping class for it).

    I hope this solves your query.

    Thanks,
    H
    HarshadR,

    Thanks for your reply. Is there any step by step example for this? So, that I can create a project on my own and learn about it?

    Thanks
    Monday, July 6, 2009 3:13 PM
  • thnx joe.. U ve provided solution for my 2 days problem..
    Friday, July 30, 2010 1:29 PM