none
Creating and using temporary tables in LINQ to SQL RRS feed

  • General discussion

  • I have some less than elegant queries that take a multiple lists of constraints to apply.  Up to this point I've been essentially modifying the SQL query and executing the query and getting the result sets back and processing them.  I decided to see if I could convert them to LINQ to SQL instead however I ran into the issue of "Queries with local collections are not supported" or that Contains was the only valid method to use with a local collection.

    Since the constraints in the lists were relatively small I felt it was more useful to send those to SQL and do the queries on that side than try to get all the data on the client and resolve them there.  (If there is a better way I would love to know).

    To achieve this I added a definition for a temporary table to the dbml file as follows:

     <Table Name="dbo.#ShipInfo" Member="ShippingInfo">
      <Type Name="ShipInfo">
       <Column Name="ID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" IsDbGenerated="false" CanBeNull="false"/>
       <Column Name="Name" Type="System.String" DbType="varchar(10) NOT NULL" IsDbGenerated="false" CanBeNull="false"/>
       <Column Name="Suffix" Type="System.String" DbType="varchar(16) NOT NULL" IsDbGenerated="false" CanBeNull="false"/>
      </Type>
    
    

    Then added a new file to extend the partial class generated from the dbml and added the function:

        public bool CreateTempTable<T>() 
        {
          string TempName = null;
          MemberInfo MIT = typeof(T);
          object[] Temp = MIT.GetCustomAttributes(typeof(TableAttribute), false);
          if (Temp.Length > 1)
          {
            throw new ArgumentException("The provided type has too many TableAttribute specifiers!", "T");
          }
          else if (Temp.Length == 0)
          {
            throw new ArgumentException("The provided type does not have a TableAttribute!", "T");
          }
          else
          {
            TempName = ((TableAttribute) Temp[0]).Name;
            string[] Parts = TempName.Split('.');
            string LastPart = Parts[Parts.Length - 1];
            if (LastPart[0] != '#')
            {
              throw new ArgumentException("The provided type does not use the temporary table name specifier '#'", "T");
            }
          }
    
    
          StringBuilder ColumnText = new StringBuilder();
          foreach (PropertyInfo PI in typeof(T).GetProperties())
          {
            object[] ColInfos = PI.GetCustomAttributes(typeof(ColumnAttribute), false);
            if (ColInfos.Length == 1)
            {
              ColumnAttribute ColInfo = (ColumnAttribute) ColInfos[0];
              if (ColumnText.Length > 0)
              {
                ColumnText.Append(',');
              }
              StringBuilder Col = new StringBuilder();
              Col.Append("\t[").Append(ColInfo.Name == null ? PI.Name : ColInfo.Name).Append(']');
              if (ColInfo.DbType != null)
              {
                Col.Append(' ').Append(ColInfo.DbType);
              }
              if (!ColInfo.CanBeNull && Col.ToString().IndexOf("NOT NULL", StringComparison.InvariantCultureIgnoreCase) < 0)
              {
                Col.Append(' ').Append("NOT NULL");
              }
              if (ColInfo.IsPrimaryKey && Col.ToString().IndexOf("PRIMARY KEY", StringComparison.InvariantCultureIgnoreCase) < 0)
              {
                Col.Append(' ').Append("PRIMARY KEY");
              }
              ColumnText.Append(Col).Append('\n');
            }
          }
    
          StringBuilder CreateTableSql = new StringBuilder();
          CreateTableSql.Append(
    @"
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'")
          .Append(TempName).AppendFormat(@"') AND type in (N'U'))
    CREATE TABLE ").Append(TempName).Append(@"(
    ");
          CreateTableSql.Append(ColumnText).Append(
    @") ON [PRIMARY]");
          this.ExecuteCommand(CreateTableSql.ToString());
          return true;
        }
    
    

    (This is certainly a rough implementation and doesn't cover all possibilities yet for the definition of the columns or handles any relationship constraints).

    From there I call my CreateTempTable function for the ShipInfo class that was auto-generated from the dbml before using it.

    Does this seem like a reasonable approach for working with data?  Is there a better mechanism to accomplish this?

    Sunday, January 2, 2011 10:54 PM

All replies

  •       if (Temp.Length > 1)

          {

            throw new ArgumentException("The provided type has too many TableAttribute specifiers!", "T");

          }

          else if (Temp.Length == 0)

          {

            throw new ArgumentException("The provided type does not have a TableAttribute!", "T");

          }

          else

          {

            TempName = ((TableAttribute) Temp[0]).Name;

            string[] Parts = TempName.Split('.');

            string LastPart = Parts[Parts.Length - 1];

            if (LastPart[0] != '#')

            {

              throw new ArgumentException("The provided type does not use the temporary table name specifier '#'", "T");

            }

          }


    Hi Brent,

    Welcome to MSDN forums.

    I find the logic is strange, when Temp.Length<0 the last else clause will be ran.

    I think use Reflector is a good way to work with data.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 5, 2011 7:27 AM
    Moderator
  • First of all, you can definitely execute regular LINQ queries on data which was retrieved from a database using LINQ to SQL. LINQ to SQL operates on IQueryable and regular LINQ operates on IEnumerable. All you have to do is use the ToList or AsEnumerable methods to permit your LINQ2SQL transform your LINQ2SQL collection for local queries. You do lose the deferred execution of the SQL query by calling the ToList method but it allows you to do everything in memory rather than hitting the database. For relatively small sets of data which are only for query and not for update, it makes sense to do it this way.

    BTW, how temporary are these tables, and do you drop them when they are done? It does seem to be a waste of resources to use the database for what are essentially in-memory types of operations.


    Robert Tanenbaum IT Developer
    Wednesday, January 5, 2011 3:15 PM
  • Hmm, I guess I never expect the length to be less than zero, that does seem anomalous to me.  It wouldn't be too hard however to change the second if condition to be <= however.
    Thursday, January 6, 2011 3:31 AM
  • Yes, I was hoping to retain the deferred execution as much as possible.  I am using locally scoped temporary tables (#<tablename>) instead of globally scoped ones (##<tablename>) so they ought to be cleaned up automatically when the session is closed (though it is better to clean them up before the session closes).  I wasn't sure if table variables would work (I thought they were only good in the scope of the current execution instead of session) though if they do using a different query to construct the declaration would be an option.
    Thursday, January 6, 2011 3:37 AM