Is there any way to use Linq to SQL without adding the .dbml file?

Question

I am working with Linq to SQL and always by adding a .dbml file which acts as the bridge in between the my application and SQL server, I am just curious whether is there any way to use Linq to SQL without using the .dbml file ? I stumbled upon Link here for the same, but it is not understandably, could some one please throw some light on this.

Answer 1

Yes of course. In fact when I was learning LINQ-to-Sql for the first time, the auto-generated code by the boiler-plate of dbml files was actually too much to digest for me at the beginning. So I started decorating my POCO classes on my own one by one and then started to learn writing the LINQ queries. Here is a quick sample to get your started:

I created a database "businessLinqToSql" with one table named Customer. Use below mentioned SQL script:

USE [businessLinqToSql]
GO

/****** Object:  Table [dbo].[Customer]    Script Date: 06/17/2016 11:28:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
    [ID] [int] NOT NULL,
    [Name] [nchar](30) NOT NULL,
    [Address] [nchar](30) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create a C# console application and add a code file named Customer.cs as below. It will be your POCO class with some declarative attributes which gives hint regarding schema of database to the LINQ-to-SQL provider:

[Table(Name="Customer")]
public class Customer
{

    [Column(IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column] 
    public string Name { get; set; }

    [Column]
    public string Address { get; set; }

}

Then you can write below LINQ query in your main function:

private static void LinqToSql()
{
    DataContext dataContext = new DataContext("data source=.;initial catalog=businessLinqToSql;integrated security=True;MultipleActiveResultSets=True");
    Table<Customer> customers = dataContext.GetTable<Customer>();
    IQueryable<string> query = from c in customers
                                       where c.Name.Length > 5
                                       orderby c.Name.Length
                                       select c.Name.ToUpper();
    foreach (string name in query) Console.WriteLine(name);
}

Answer 2

If you need to query using the properties of your context (context.TableName), then NO.

You need to have the classes generated in some place, either automatically or manually (Entity Framework Code First Approach).

But there is no magic that lets the context have all your tables without a generated class file(s) [which is part of the dbml in your case]

However, you can create your own classes and use the TableAttribute. This is you manually replicating what the dbml is doing. Check this link for more.

Some example from the above link:

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

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>(); } }
}

Usage:

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