Sql – How to make linq to sql classes internal or private

linq-to-sql

I found this question/answer about setting linq to sql classes as internal, but one thing I found is that classes generated for stored procedure return types are set as public, and even though I set the access modifier for the stored procedure and the data contest class as internal, the generated class for the stored proc is still public. How can I make it internal. I'm doing this in a seperate data access layer assembly, so I don't want these classes exposed to applicaitons.

Best Answer

Create a partial classes to call the stored procedures. I would generate the code as normal then paste them into a separate file and then delete the stored procedures from the design surface. You can then set the access modifiers as you wish. This is also a useful technique for dealing with stored procedures whereLINQ to SQL does not generate the classes as you would expect, such as those that use temporary tables.

internal partial class DataClasses2DataContextExtended : System.Data.Linq.DataContext {

    [Function(Name="dbo.SPDEMO")]
    internal ISingleResult<SPDEMOResult> SPDEMO()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return ((ISingleResult<SPDEMOResult>)(result.ReturnValue));
    }
}

internal partial class SPDEMOResult
{

    public SPDEMOResult()
    {
    }

    public System.Nullable<int> FilmID
    { get; set; }


    public string FilmName
    { get; set; }
}

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – LINQ to SQL: Stored Procedure Results

Can you edit this in the dbml? Personally, I tend to treat the auto-generated types (from functions and stored procedures) as DTOs that are local to the DAL, so I immediately re-map them to my own POCO representation - i.e.

var qry = from row in ctx.SomeProc(12345)
          select new Foo {ID = row.ID, Name = row.Name };

etc. Re the second question "Also, how can you perform a linq query on the result set of the stored procedure?" - I would recommend using a UDF instead of a stored procedure if you want to compose it: this allows you to do the composition at the database, for example paging and filtering:

var qry = (from row in ctx.SomeFunction(12345)
          where row.IsActive
          select row).Skip(10).Take(10);

which should (in LINQ-to-SQL at least) do everything in TSQL at the server. Otherwise, you can call AsEnumerable() and use LINQ-to-Objects at the calling .NET layer:

var qry = (from row in ctx.SomeProc(12345).AsEnumerable()
          where row.IsActive
          select row).Skip(10).Take(10);

To edit the dbml (which is just xml), change the ElementType/@Name here:

<Function Name="dbo.CustOrderHist" Method="CustOrderHist">
  <Parameter Name="CustomerID" Parameter="customerID" Type="System.String" DbType="NChar(5)" />
  <ElementType Name="FooBar"> <!-- ********** HERE ************ -->
      <Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
      <Column Name="Total" Type="System.Int32" DbType="Int" CanBeNull="true" />
  </ElementType>
</Function>

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – LINQ to SQL: Stored Procedure Results

Related Topic